C

C#

command line compiler, 363

options for writing code for SQL Server, 360

C++, used with ODBC or OLE DB, 10

Cache class

expiration methods, 108

System.Web.Caching namespace, 102

caching, 99–114

adding items to cache, 102

custom, 102–105

exercise evaluating effects of, 112–114

exercise using OutputCache directive, 109–112

hardware read cache, 448

hardware write cache, 448

output caching, 99

overview of, 99

page-level output caching, 99

Procedure Cache Manager, 455

query notifications and, 105–108

references, 141

refresh strategy and, 108

removing items from cache, 103

storing DataSets in cache, 103

user control-level output caching, 100–101

call back feature, asynchronous processing, 186–189

CAS (Code Access Security)

CLR (Common Language Runtime) and, 429

benefits of procedures vs. XPs, 433

CLR Host and, 428

CASCADE action, foreign key constraints, 198

CASE functions, as alternatives to cursors, 118

case scenarios

in Exam 70–442, xxiii

structure of, xxiv

case sensitivity, XML and XQuery, 83

catalog views

function catalog views in partitions, 348

object catalog views in partitions, 348

sys.partitions, 346

catch blocks. See try/catch blocks

CD, using CD accompanying this book, xxiv

Cellset object, 158

central processing units. See CPUs (central processing units)

certificates, 471–472

encryption key types, 472

information contained in, 471

trust and, 472

certification, Microsoft Certified Professional, xxvi

Certification Mode, of practice tests, xxv

check constraints, 199–208

advantages of, 199

disadvantages of, 200

execution of, 201

extending with user-defined functions, 200–202

for improving query performance, 206–208

overview of, 199

performance impact of, 202

checkpoints

flushing data pages to disk, 451

storage engine, 449

class identifiers (CLSIDs), OLE DB 10

classes

AMO class library, 154–157

RMO class library, 150

SQL Server Profiler event classes, 372

CLI (Common Language Infrastructure), 426. See also CLR (Common Language Runtime)

clients

client-side precautions for preventing SQL injection attacks, 212

connections for heterogeneous client computers, 19–22

protocol configuration, 4

CLR (Common Language Runtime), 426–444

application domains supported, 430

assembly permissions, 429

CAS (Code Access Security), 430

data sources as factor in when to use, 437

database object types, 430

design goals for, 426

enabling, 361

examples of code, 434

HPAs (Host Protection Attributes), 429

memory usage statistics, 441–443

new hardware trends, 406

operation with side effects, 438

overview of, 425

reliability of, 426

scalability of, 426

security of, 427

services, 429

table-valued functions, 297

transaction management, 438–441

T-SQL compared with, 430–434

type-safe code, 429

typing and, 438

usability of code, 437

when to use code, 437

XPs compared with procedures, 433

CLSIDs (class identifiers), OLE DB 10

clustered indexes

appropriate uses of, 312

compared with non-clustered, 305

covered indexes and, 305

created by primary keys, 196

exercise comparing with non-clustered indexes, indexed views, and non-indexed baseline, 330

exercise optimizing query, 325–327

index performance and, 312–313

organization of, 463

scan option, 467

structures of, 464

COALESCE function, SELECT statements, 59

code

determining which tier to use for efficient operation, 360

exercise creating user-defined function, 367–370

moving code to different tiers and, 360–363

reuse in multi-tier architectures, 360

Code Access Security. See CAS (Code Access Security)

COLLATE operator, in filtered queries and, 291

collation, computed columns and, 318

columns

clustered indexes and, 313

computed columns in index performance, 316–320

data type size when indexing, 335

in covered indexes, 305

index levels of included columns, 310

index levels of non-included columns, 309

index performance and, 307–312

index size matrix comparing included and non-included columns, 309

maximum columns in an index, 307

used for filtering, grouping, or sorting in index key, 308

command line compilers, 363

Command object, asynchronous processing, 185

Common Language Infrastructure (CLI), 426. See also CLR (Common Language Runtime)

Common Language Runtime. See CLR (Common Language Runtime)

common table expressions. See CTEs (common table expressions)

Common Type System (CTS), 426

compatibility level, database settings, 57

compilers

command line, 363

recompilation, 460

computed columns

applied to indexing, 316

collations and, 318

foreign key constraints on, 197

index performance, 316–320

primary and unique constraints on, 196

concurrency, 234–236

cursors, 134

dirty reads, 235

locks for solving concurrency problems, 235

lost updates, 234

non-repeatable reads, 235

overview of, 234

phantom reads, 235

READ_ONLY concurrency option, 120

update conflicts and, 246–250

connected models, ADO.NET, 35–36

connections

exercise consuming HTTP endpoint, 28–30

exercise creating HTTP endpoint, 27–28

for heterogeneous client computers, 19–22

for HTTP Web services, 22–24

for SQL Server named instances, 25–26

constraints

check constraints, 199–208

for implementing data integrity, 195

foreign key constraints, 196–199

primary key and unique key constraints, 195

types of, 195

CONTAINS predicate, in full-text queries, 63

CONTAINS TABLE predicate, in full-text queries, 64

correlated subqueries, 292–295

costs

index usage, 322

query costs, 285, 322

counters, System Monitor

adding, 378

defaults, 378

diagnosis with, 382

performance baselines with, 381

covered indexes

compared with non-covered, 307

exercise using non-clustered index for optimizing query, 327

index performance and, 305–307

performance benefits of, 306

CPUs (central processing units)

bottlenecks, 384

configuring processor affinity, 413

expanding limits for 32-bit CPUs, 407

multiple threads, 407

NUMA and, 408

query costs and, 285

CREATE VIEW statement, 354

CREATE XML SCHEMA COLLECTION, 80

CROSS APPLY operators, in SELECT statements, 56

csc.exe, C# compiler, 363

CTEs (common table expressions)

queries placed in, 294

recursive queries and, 118

CTS (Common Type System), 426

Cube

AMO objects, 154

AMO OLAP class, 155

CursorRecompile trace event, 461

cursors, 115–140

alternatives to, 116

building with dynamic SQL, 131

CASE function as alternative to, 118

case scenario evaluating cursor performance, 140

CLOSE and DEALLOCATE, 121

comparing execution time of cursor with alternative methods, 122

concurrency, 134

CursorRecompile trace event, 461

dynamic management functions, 123

efficient use of, 129

evaluating efficiency of, 133

evaluating use of, 122

exercise comparing with alternatives, 125–127

exercise creating, 124–125

exercise creating scrollable cursor, 136

exercise examining, 138

FORWARD_ONLY or FAST_FORWARD types, 121

function of, 97

MARS compared with, 170

minimizing blocking, 133

minimizing or eliminating, 135

overview of, 115

performance maximization, 120

processing on row-by-row basis with, 130

query performance and, 297

READ_ONLY concurrency option, 120

recursive queries as alternative to, 118–119

reducing amount of data in, 120

references, 141

scrollable, 129

SELECT statements as alternative to, 116–117

server memory and, 133

Transact-SQL code for declaring, 115

types of, 132

when to use, 129

WHILE loops as alternative to, 117

Custom Mode, of practice tests, xxv

CXPacket waits, 423