DOC PREVIEW
Rose-Hulman CSSE 333 - Planning Indexes

This preview shows page 1-2-14-15-30-31 out of 31 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 31 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 31 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 31 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 31 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 31 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 31 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 31 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

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 IndexesOverviewIntroduction to IndexesIndex ArchitectureHow SQL Server Retrieves Stored DataHow SQL Server Maintains Index and Heap StructuresDeciding Which Columns to IndexIntroduction to IndexesHow SQL Server Stores and Accesses DataWhether to Create IndexesHow SQL Server Stores and Accesses DataHow Data Is StoredRows are stored in data pagesHeaps are a collection of data pages for a tableHow Data Is AccessedScanning all data pages in a tableUsing 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 IndexesWhy to Create an IndexSpeeds up data accessEnforces uniqueness of rowsWhy Not to Create an IndexConsumes disk spaceIncurs overhead Index ArchitectureSQL Server Index ArchitectureUsing HeapsUsing Clustered IndexesUsing 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 storedNavigate through the heap and find available space for new rows being insertedConnect data pagesReclaims Space for New Rows in the Heap When a Row Is DeletedUsing Clustered IndexesEach Table Can Have Only One Clustered IndexThe Physical Row Order of the Table and the Order of Rows in the Index Are the SameKey Value Uniqueness Is Maintained Explicitly or ImplicitlyUsing Nonclustered IndexesNonclustered Indexes Are the SQL Server DefaultExisting Nonclustered Indexes Are Automatically Rebuilt When:An existing clustered index is droppedA new clustered index is createdThe DROP_EXISTING option is used to change which columns define the clustered index How SQL Server Retrieves Stored DataHow SQL Server Uses the sysindexes TableFinding Rows Without IndexesFinding Rows in a Heap with a Nonclustered IndexFinding Rows in a Clustered IndexFinding Rows in a Clustered Index with a Nonclustered IndexHow SQL Server Uses the sysindexes TableDescribes the IndexesLocation of IAM, First, and Root of IndexesNumber of Pages and RowsDistribution 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

Rose-Hulman CSSE 333 - Planning Indexes

Download Planning Indexes
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Planning Indexes and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Planning Indexes 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?