T


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




MCITP Self-Paced Training Kit Exam 70-442  .Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
MCITP Self-Paced Training Kit Exam 70-442 .Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
ISBN: 073562383X
EAN: N/A
Year: 2007
Pages: 162

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net