Module 6: Planning IndexesOverviewIntroduction to IndexesHow SQL Server Stores and Accesses DataWhether to Create IndexesIndex ArchitectureMultimedia Presentation: SQL Server Index ArchitectureUsing HeapsUsing Clustered IndexesUsing Nonclustered IndexesHow SQL Server Retrieves Stored DataHow SQL Server Uses the sysindexes TableFinding Rows Without IndexesFinding Rows in a Heap with a Nonclustered IndexFinding Rows in a Clustered IndexFinding Rows in a Clustered Index with a Nonclustered IndexHow SQL Server Maintains Index and Heap StructuresPage Splits in an IndexForwarding Pointer in a HeapHow SQL Server Updates RowsHow SQL Server Deletes RowsDeciding Which Columns to IndexUnderstanding the DataIndexing GuidelinesChoosing the Appropriate Clustered IndexIndexing to Support QueriesDetermining SelectivityDetermining DensityDetermining Distribution of DataRecommended PracticesReviewModule 6: Planning IndexesOverviewIntroduction to IndexesIndex ArchitectureHow SQL Server Retrieves Stored DataHow SQL Server Maintains Index and Heap StructuresDeciding Which Columns to IndexIntroduction to IndexesHow SQL Server Stores and Accesses DataWhether to Create IndexesHow SQL Server Stores and Accesses DataHow Data Is StoredRows are stored in data pagesHeaps are a collection of data pages for a tableHow Data Is AccessedScanning all data pages in a tableUsing an index that points to data on a pageData PagesPage 7 Page 8 Page 9AkhtarAkhtarFunkFunkSmithSmithMartinMartin......Page 4 Page 5 Page 6 ..............................ConConFunkFunkWhiteWhite............RuddRuddWhiteWhiteBarrBarr............SmithSmithOtaOtaJonesJones............MartinMartinPhuaPhuaJonesJonesSmithSmith......GanioGanioJonesJonesHallHall..................................................................................................................................................................Whether to Create IndexesWhy to Create an IndexSpeeds up data accessEnforces uniqueness of rowsWhy Not to Create an IndexConsumes disk spaceIncurs overhead Index ArchitectureSQL Server Index ArchitectureUsing HeapsUsing Clustered IndexesUsing Nonclustered IndexesMultimedia Presentation: SQL Server Index ArchitectureUsing HeapsSQL Server:Uses Index Allocation Map Pages That:Contain information on where the extents of a heap are storedNavigate through the heap and find available space for new rows being insertedConnect data pagesReclaims Space for New Rows in the Heap When a Row Is DeletedUsing Clustered IndexesEach Table Can Have Only One Clustered IndexThe Physical Row Order of the Table and the Order of Rows in the Index Are the SameKey Value Uniqueness Is Maintained Explicitly or ImplicitlyUsing Nonclustered IndexesNonclustered Indexes Are the SQL Server DefaultExisting Nonclustered Indexes Are Automatically Rebuilt When:An existing clustered index is droppedA new clustered index is createdThe DROP_EXISTING option is used to change which columns define the clustered index How SQL Server Retrieves Stored DataHow SQL Server Uses the sysindexes TableFinding Rows Without IndexesFinding Rows in a Heap with a Nonclustered IndexFinding Rows in a Clustered IndexFinding Rows in a Clustered Index with a Nonclustered IndexHow SQL Server Uses the sysindexes TableDescribes the IndexesLocation of IAM, First, and Root of IndexesNumber of Pages and RowsDistribution of DataindidindidindidindidObject TypeObject TypeObject TypeObject Type00HeapHeap11Clustered IndexClustered Index2 to 2502 to 250Nonclustered IndexNonclustered Index255255text, ntext, or imagetext, ntext, or imageFinding Rows Without IndexesHeapExtent 127ididindid = 0indid = 0First IAMFirst IAMsysindexesIAMExtent 128 Extent
View Full Document