S

SANs (storage area networks)

I/O related problems, 411

scalable shared databases accessed over, 359

SARGs (searchable arguments)

query performance, 74–76

optimizing query performance, 290

scalability, 343–371

CLR (Common Language Runtime), 426

DDR (Data-Dependent Routing), 355–356

DPV (distributed partition view), 353–355

exercise creating partition function, 364

exercise creating user-defined function, 367–370

linked servers and, 349–352

moving code to different tiers and, 360–363

NUMA and, 408

overview of, 343

peer-to-peer replication, 359–360

procedures compared with XPs, 433

scalable shared databases, 358

server federations and, 349–352

SODA (Service-Oriented Database Architecture), 357–358

techniques for, 343

Transact-SQL compared with SQLCLR, 364

scalable shared databases, 358

scalar functions

check constraints and, 200

defined, 62

query performance and, 294–296

ScalarMiningStructureColumn, AMO data mining class, 155

scan option, clustered indexes, 467

scans, SARG, 290

scatter-gather, hardware capabilities for, 449

schedulers, SQLOS, 414–416

schema stability locks (Sch-S), 239

schemes, partition, 346

scripting languages

ODBC not used with, 9

used with, 11

SCROLL_LOCKS, concurrency options, 134

scrollable cursors

exercise creating, 136

fetch options, 129

overview of, 115, 129

searchable arguments. See SARGs (searchable arguments)

secondary XML indexes, 470

Secure Sockets Layer. See SSL (Secure Sockets Layer)

security

CLR (Common Language Runtime), 427

AMO classes, 156

procedures compared with XPs, 433

protocols for securing replication topology, 153

seek, SARGs, 290

SELECT statements, 49–69

APPLY operators, 55–57

as alternative to cursors, 116–117, 133

COALESCE function, 59

correlated subqueries and, 292

cursors representing rows returned from, 115

EXCEPT and INTERSECT operators, 57

execution order of, 286

exercise using APPLY operator, 67

exercise using JOIN operator, 65

full-text queries and, 64

functions and, 61–63

ISNULL functions, 60

JOIN operators, 49

linked servers for accessing multiple data sources, 51–54

ORDER BY clause in, 288

PIVOT/UNPIVOT operators, 54

query engine and, 455

query hints and, 71

ranking functions and, 58

steps in processing, 457

subqueries, 50

syntax, 49

usability of code and, 437

what defined by, 455

Send method, CLR, 362

SendResultsEnd method, CLR, 363

SendResultsRow method, CLR, 363

SendResultsStart method, CLR, 362

serializable isolation level, 242–243

serializable locking hint, protecting against phantom reads, 273

Server Management Objects. See SMO (Server Management Objects)

Server object, SMO, 145

server processor identifier (SPID), 413

servers

cursors as server-side objects, 115

cursor demands on server memory, 133

distributed transactions in T-SQL, 267

targeting multiple servers to accommodate high demand, 349

Service Broker

asynchronous processing and, 184

services provided by, 357

SMO assembly supporting, 146

Service-Oriented Architecture (SOA), 357

Service-Oriented Database Architecture. See SODA (Service-Oriented Database Architecture)

services, ADO.NET, 6

services, SQL Server. See SQL Server services

SET _TIMEOUT statement, locks, 236

SET DEFAULT, foreign key constraints, 197

SET NULL, foreign key constraints, 197

SET STATISTICS IO ON statements

page reads metric, 285

performance metric, 307

query cost and, 294

SET STATISTICS TIME ON statements

metric for query execution time, 286

query cost and, 294

SET TRANSACTION ISOLATION LEVEL statement, 234

shared locks (S-locks)

acquiring/releasing for read operations, 236

solving concurrency problems, 235

shared memory, network protocols, 3

Simple Object Access Protocol (SOAP), 21

smallint data types, 62

SMO (Server Management Objects), 145–149

assemblies, 145

connecting to a server instance, 147

exercise creating database using SMO application, 160–166

namespaces, 147

overview of, 145

tasks performed with, 149

SMP (Symmetric Multi Processing)

hardware trends, 407

NUMA as improvement to, 408

SNAC (SQL Native Client)

as data provider, 5

data access with, 12

exercise connecting using, 16–17

MARS enabled for use with OLEDB provider, 171

MARS enabled for use with SNAC ODBC provider, 171

recommended uses as data provider, 5

snapshot isolation level, 244–250

applying in a database, 244

overview of, 244

update conflicts and, 246–250

SOA (Service-Oriented Architecture), 357

SOAP (Simple Object Access Protocol), 21

SODA (Service-Oriented Database Architecture), 357–358

benefits of, 357

drawbacks of, 357

how it works, 358

overview of, 357

software

evaluation editions, xxvii

impact of hardware tends on software design, 408

sp_addmessage stored procedure, 225

sp_altermessage stored procedure, 225

sp_configure system stored procedure, 352

sp_create_plan_guide stored procedure, 73

sp_dropmessage stored procedure, 225

SPID (server processor identifier), 413

SQL injection attacks

implementing procedural data integrity and, 211–213

preventing, 211

vulnerability to, 132

SQL Native Client. See SNAC (SQL Native Client)

SQL Server 2005

care in use of cursors, 133

concurrency control, 120

connections for named instances, 25–26

database encryption. See encryption, SQL Server 2005 databases

database engine. See database engine

dynamic management functions, 123

evaluating effective use of cursors, 122

index internals. See indexes

linked servers, 25

query engine. See query engine

query notification feature, 105–108

storage engine. See storage engine

XML Web services, 22

SQL Server 2005 Installation Wizard

AMO installed with, 154

replication services installed with, 150

SQL Server Agent jobs, 149

SQL Server Analysis Services. See SSAS (SQL Server Analysis Services)

SQL Server Business Intelligence Development Studio

data mining, 155

exercise creating data mining structure, 166

SQL Server Configuration Manager

client protocol configuration, 4

network protocol configuration, 3

SQL Server Enterprise Edition

installing, xxi

software requirements for Exam 70–442, xxi

SQL Server instance, 147

SQL Server Integration Services (SSIS), 352

SQL Server Management Studio

AMO as alternative to

creating linked servers, 51

displaying query execution plan, 70

identifying NULL values in query results, 60

creating linked servers, 25

SMO for administering tasks normally done with, 145

SQL Server Profiler, 372–377

creating performance baselines, 380

exercise analyzing performance with, 386–390

overview of, 372

resource use in traces, 377

starting new trace, 374

templates, 373

trace property selection, 376

tracing cursor performance problems, 135

ways to execute, 373

SQL Server services

AMO (Analysis Management Objects). See AMO (Analysis Management Objects)

asynchronous processing. See asynchronous processing

RMO (Replication Management Objects). See RMO (Replication Management Objects)

SMO (Server Management Objects). See SMO (Server Management Objects)

SQL-92 syntax, 134

SqlClient .NET Provider, 172

SQLCLR (SQL Common Language Runtime). See also CLR (Common Language Runtime)

benefits of, 357

exercise creating user-defined function, 367–370

Transact-SQL compared with, 364

SQLOS (SQL Operating System), 408–425

application configuration strategy, 419

application performance and, 416–425

architecture, 409–411

configuring soft-NUMA support, 411–413

determining degree of parallelism in currently executing queries, 423

hierarchical relationship between nodes, schedulers, and tasks, 409

new hardware trends, 406

overview of, 408

querying wait types, 417

schedulers, 414–416

SQL Server task scheduling, 413

synchronization objects, 427

UMS compared with, 417

workload distribution, 420

SqlParameter objects, ADO.NET, 211

SSAS (SQL Server Analysis Services)

DataSource and DataSourceView objects, 158

programmatically controlling SSAS objects. See AMO (Analysis Management Objects)

references, 154

SSIS (SQL Server Integration Services), 352

SSL (Secure Sockets Layer)

certificates, 20

configuring SQL Server 2005 for, 473–475

stable media, storage engine, 444

static cursors

cursor types, 132

when to use, 133

Statistics Manager, query engine, 455

storage area networks. See SANs (storage area networks)

storage engine, 443–450

ACID properties, 443

buffer pool (BPool), 447

checkpoints, 449

data page sizes and numbers, 447

eager writes, 450

extents, 447

FUA (Forced Unit Access), 446

hardware read cache, 448

hardware trends, 406

hardware write cache, 448

latches, 449

lazy writer, 449

log parity, 446

LSN (Log Sequence Number), 448

mirroring and remote mirroring, 446

multichannel and load balancing systems, 445

object ID, 447

overview of, 443

Point in Time, 444

stable media, 444

tasks of, 443

Torn I/O, 445

WAL (Write-Ahead Logging), 448

write ordering (write dependency), 444

stored procedures

data validation in, 209

exercise creating, 228–229

for creating, modifying, deleting error messages, 225

object types, 430

processing on row-by-row basis, 130

SP:Recompile trace event, 461

using XACT_ABORT ON with, 220

verifying security settings in, 210

XPs executing out of band, 426

strings

disallowing unneeded characters in preventing SQL injection attacks, 211

empty strings compared with NULL values, 60

XML strings as query hints, 71

Study Mode, of practice tests, xxv

subqueries

correlated with outer query, 292–294

SELECT statements, 50

syntax for, 50

without correlation to outer query, 292

Surface Area Configuration tool, 361

symmetric key encryption, 472

Symmetric Multi Processing. See SMP (Symmetric Multi Processing)

synchronization objects, SQLOS, 427

sys.dm_db_index_physical_stats, 333

sys.dm_db_index_usage_stats, 323

sys.dm_exec_cursors function, 123

sys.partitions, catalog view, 346

sysadmin role, 19

system DSN, 8

System Monitor, 377–379

adding counter, 378

counters for creating performance baselines, 381

counters for diagnosing problems, 382

creating performance baselines, 380

default counters, 378

overview of, 377

system requirements, Exam 70–442, xix

System.Transactions namespace, 268, 438

System.Web.Caching namespace, 102