table locks. See locks
table scan, indexes, 467
TableMiningStructureColumn, AMO data mining, 155
tables
applying data integrity rules to, 195, 214
heaps and clustered, 305
maximum columns in, 307
row width and, 332
table-valued functions. See TVFs (Table-Valued Functions)
Tabular Data Stream (TDS), 414
task scheduling, SQL Server, 413
TCP (Transmission Control Protocol), endpoints, 19
TCP/IP
communication protocol, 3
port affinity, 414
TDS (Tabular Data Stream), 414
technical support, Microsoft Press, xxvii
tempdb
limited space for, 385
snapshot transaction level and, 244
templates, SQL Server Profiler, 373
theoretical execution order, queries, 286
threads
multiple threading CPUs, 407
SQLOS synchronization and, 427
tiers, improving performance by moving code to different tiers, 360–363
timeouts, locks, 236
traces
cursor performance, 135
resource use and, 377
selecting trace properties, 376
SQL Server Profiler, 372
SQL:StmtRecompile, 460
starting new trace, 374
transactions, 233–282
applying locking hints, 265
case scenario for optimizing locking, 280
CLR for managing, 438–441
concurrency problems, 234
distributed, 267–269
excessive blocking, 384
exercise acquiring locks using read committed snapshot isolation level, 256–258
exercise applying default isolation level, 269–273
exercise applying locking hints, 273–276
exercise using read committed isolation level, 253–256
exercise using stored procedure to protect against deadlocks and phantom reads, 276–280
extensive locking problems, 252
isolation levels, 234, 444
locking hints, 260
MARS for avoiding transaction locks, 170
minimizing deadlocks, 266
.NET Framework transactions used with MARS, 173
OUTPUT keyword for optimizing locking, 261, 266
overview of, 233
read committed isolation level, 236–238
read committed snapshot isolation level, 250–252
read uncommitted isolation level, 238–239
repeatable read isolation level, 240–241
serializable isolation level, 242–243
snapshot isolation level, 244–250
Transact-SQL. See T-SQL (Transact-SQL)
triggers, 430. See also DML triggers
try/catch blocks
error handling, 221–224
exercise creating, 229–231
implementing in SQL Server 2005, 221
limitations in SQL Server 2005, 224
UPDATE or DELETE statement within snapshot transaction, 248
T-SQL (Transact-SQL)
aggregate functions, common problems with, 435
APPLY operator, 55
CREATE XML SCHEMA COLLECTION, 80
declaring cursors, 115
derived tables as alternative to cursors, 116
enabling ad hoc queries, 352
establishing query notification, 105
extended syntax for cursor declaration and, 134
implementing procedural data integrity, 208
inline table valued function for query optimization, 297
LIKE predicate, 468
Multi-statement-statement table-valued function, 297
not designed for object-oriented programming, 133
PIVOT operator, 54
query execution plans and, 71
queries, 364
SELECT statements. See SELECT statements
server-side distributed transactions, 267
as set-based language, 364
SCROLL_LOCKS option, 134
SQLCLR compared with, 364, 430–434
verifying permissions, 210
tuning queries. See also query performance
exercise rewriting query, 76
self-tuning capability of SQL Server, 70
TVFs (Table-Valued Functions)
APPLY operators and, 56
defined, 62
example application, 434
object types, 430
typed datasets, 33
type-safe code, CLR, 429