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 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 9AkhtarFunkSmithMartin...Page 4 Page 5 Page 6 ...............ConFunkWhite......RuddWhiteBarr......SmithOtaJones......MartinPhuaJonesSmith...GanioJonesHall.................................................................................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 IndexArchitectureUsing HeapsSQL Server: Uses Index Allocation Map Pages That: Contain information on where the extents of a heapare stored Navigate through the heap and find available space fornew rows being inserted Connect data pages Reclaims Space for New Rows in the Heap When a RowIs DeletedUsing Clustered Indexes Each Table Can Have Only One Clustered Index The Physical Row Order of the Table and the Order ofRows in the Index Are the Same Key Value Uniqueness Is Maintained Explicitlyor ImplicitlyUsing Nonclustered Indexes Nonclustered Indexes Are the SQL Server Default Existing Nonclustered Indexes Are AutomaticallyRebuilt When: An existing clustered index is dropped A new clustered index is created The DROP_EXISTING option is used to change whichcolumns 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 NonclusteredIndexHow SQL Server Uses the sysindexes Table Describes the Indexes Location of IAM, First, and Root of Indexes Number of Pages and Rows Distribution of DataindidindidObject TypeObject Type0 Heap1 Clustered Index2 to 250 Nonclustered Index255 text, ntext, or imageFinding Rows Without IndexesHeapExtent 127id indid = 0 First IAMsysindexesIAMExtent 128 Extent 129010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......010203……ConFunkWhite......………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......0102030405ConFunkWhiteDurkinLang………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......010203……ConFunkWhite......………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......0102030405DunnRandallOtaSlichterLaBrie………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......010203……ConFunkWhite......………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......010203……ConFunkWhite......………......010203……SmithOtaJones…...………......01020304…AkhtarFunkSmithMartin...………......010203……RuddWhiteBarr......………......010203……GraffBaconKoch......………......Extent 13001020304…SeattleParisTokyoAtlanta...………......…127 1128 1129 0130 1…Extent Bit MapFinding Rows in a Heap with a Nonclustered Index Non-LeafLevelPage 12 - RootPage 37 Page 28Leaf Level(Key Value)Page 41 Page 51 Page 61 Page 71 Akhtar...MartinAkhtarBarrConFunkFunk4:706:014:705:034:704:014:706:024:704:02MartinSmith...SmithSmithSmithWhiteWhite4:706:034:708:044:707:014:704:034:705:02AkhtarGanio...GanioHallJonesJonesJones4:709:014:709:044:709:024:708:034:707:03HeapPage 707 Page 708 Page 70901020304..................AkhtarFunkSmithMatey...Page 704 Page 705 Page 706 010203.....................ConnFunkWhite......010203.....................RuddWhiteBarr......010203.....................SmithOtaJones......01020304..................MartinPhuaJonesSmith...010203.....................GanioJonesHall......MartinMateyOtaPhuaRudd4:708:014:706:044:707:024:708:024:705:01NonClusteredIndexFile ID #4id indid = 2 rootsysindexesSELECT lastname, firstnameFROM memberWHERE lastnameBETWEEN 'Masters' AND 'Rudd' Non-LeafLevelPage 12 - RootPage 37 Page 28Leaf Level(Key Value)Page 41 Page 51 Page 61 Page 71 Akhtar...MartinAkhtarBarrConFunkFunk4:706:014:705:034:704:014:706:024:704:02MartinSmith...SmithSmithSmithWhiteWhite4:706:034:708:044:707:014:704:034:705:02AkhtarGanio...GanioHallJonesJonesJones4:709:014:709:044:709:024:708:034:707:03HeapPage 707 Page 708 Page 70901020304..................AkhtarFunkSmithMatey...Page 704 Page 705 Page 706 010203.....................ConnFunkWhite......010203.....................RuddWhiteBarr......010203.....................SmithOtaJones......01020304..................MartinPhuaJonesSmith...010203.....................GanioJonesHall......MartinMateyOtaPhuaRudd4:708:014:706:044:707:024:708:024:705:01NonclusteredIndexFile ID #4MartinMartinMartinMartin0404......MateyMateyMateyMatey4:706:044:706:040202......PhuaPhuaPhuaPhua4:708:024:708:020101......RuddRuddRuddRudd4:705:014:705:010202......OtaOtaOtaOta4:707:024:707:02Finding Rows in a Clustered IndexClustered IndexPage 140 - RootPage 100 Page 120 Page 130 Page 141 Page 145AkhtarBarrConFunkFunk...23345678253413341534.....................MartinMartinOtaPhuaRudd...12347778587878786078.....................SmithSmithSmithWhiteWhite...14345778797822341634.....................AkhtarGanio……Akhtar…MartinMartinSmith…Page 110


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?