I

I/O (input/output)

bottlenecks, 384

multichannel support in SQL Server 2005 Storage Engine, 445

NUMA affecting I/O performance, 411

query costs and, 285

Torn I/O, 445

IAM (Index Allocation Map), 464

IDE (Integrated Drive Electronics), 447

IIS (Internet Information Services), software requirements for Exam 70–442, xxi

included column feature, indexes, 466

Index Allocation Map (IAM), 464

index depth, 307–312

index keys

columns in, 308

defined, 196

updating, 313

index performance, 305–331

analyzing index usage, 322–323

clustered indexes, 312–313

columns and index depth and, 307–312

computed columns, 316–320

covered indexes, 305–307

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

exercise creating performance baseline for queries, 324–325

exercise using clustered index for optimizing query, 325–327

exercise using indexed view for optimizing query, 328–330

indexed views and, 320–322

maximum columns in indexes, 307

non-clustered index for optimizing query, 327–328

overview of, 305

read performance vs. write performance, 313–316

size matrix for included and non-included columns, 309

indexed views

creating, 320

exercise optimizing query, 328–330

index performance and, 320–322

query cost and, 322

verifying use of, 330

indexes, 462–470

associating indexes with partition schemes, 346

clustered indexes created by primary keys, 196

clustered-index structures, 464

foreign keys and, 197

full-text indexes, 468

full-text queries using, 63

hardware trends, 406

heap structures, 464

included columns indexes, 466

index scan, 75

non-clustered index structures, 465

non-clustered indexes created with unique keys, 195

optimizing index structures, 467

optimizing structures of, 467

organization of, 463

overview of, 462

primary XML indexes, 469

secondary XML indexes, 470

SQL Server 2005 Enterprise Edition supporting parallel operations, 422

types of, 462

XML indexes, 469

inner joins

overview of, 50

query optimization and, 291

ranking functions and, 64

input/output. See I/O (input/output)

INSERT statements

OUTPUT keyword, 261

query hints and, 71

steps in processing, 457

subqueries, 50

usability of code and, 437

INSTEAD OF triggers, DML, 210

Integrated authentication method, endpoints and, 20

Integrated Drive Electronics (IDE), 448

INTERCEPT operators, in SELECT statements, 57

Internet Explorer, software requirements for Exam 70–442, xxi

Internet Information Services (IIS), software requirements for Exam 70–442, xxi

Interoperability, multi-tier architectures and, 360

IS_MEMBER function, verifying security settings in stored procedures, 210

ISNULL function, in SELECT statements, 60

isolation levels, transactions

excessive blocking and, 384

exercise acquiring locks using read committed snapshot, 256–258

exercise applying default isolation level, 269–273

exercise applying locking hints, 273–276

exercise using read committed, 253–256

extensive locking problems, 252

list of, 234

locking hints, 260

locks and, 236

minimizing deadlocks, 266

OUTPUT keyword for optimizing locking, 261, 266

read committed, 236–238, 250–252

read uncommitted, 238–239

repeatable read, 240–241

serializable, 242–243

snapshots, 244–250