Module 6 Planning Indexes Overview 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 Indexes How 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 page Data Pages Page 4 Con Funk White Page 5 Rudd White Barr Page 6 Akhtar Funk Smith Martin Page 8 Page 7 Smith Ota Jones Martin Phua Jones Smith Page 9 Ganio Jones Hall 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 Indexes Multimedia Presentation SQL Server Index Architecture Using Heaps SQL 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 Deleted Using 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 Implicitly Using 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 Index How SQL Server Uses the sysindexes Table Describes the Indexes indid Object Type 0 Heap 1 Clustered Index 2 to 250 Nonclustered Index 255 text ntext or image Location of IAM First and Root of Indexes Number of Pages and Rows Distribution of Data Finding Rows Without Indexes sysindexes id indid 0 IAM Extent 127 128 129 130 Heap Extent 127 01 Con 01 Rudd 01 Akhtar 02 01 Funk Smith 02 01 White Con 02 01 Funk Rudd 03 02 White 01 Ota Akhtar 03 02 Barr 01 Funk Smith 03 02 Smith White 04 03 Durkin 02 Jones Funk 04 03 02 White Ota 03 Martin 05 Lang 03 Barr Smith 03 Jones Martin 04 Extent 128 01 Dunn 01 Rudd 01 Akhtar 02 01 Randall Smith 02 01 White Con 02 01 Funk Rudd 03 02 Ota 01 Ota Akhtar 03 02 Barr 01 Funk Smith 03 02 Smith White 04 03 Slichter 02 Jones Funk 04 03 02 White Ota 03 Martin 05 LaBrie 03 Barr Smith 03 Jones Martin 04 First IAM Bit Map 1 1 0 1 Extent 129 01 Seattle 01 Rudd 01 Akhtar 02 01 Paris Smith 02 01 White Con 02 01 Funk Rudd 03 02 Tokyo 01 Ota Akhtar 03 02 Barr 01 Funk Smith 03 02 Smith White 04 03 Atlanta 02 Jones Funk 04 03 02 White Ota 03 Martin 03 Barr Smith 03 Jones 04 Martin Extent 130 01 Graff 01 Rudd 01 Akhtar 02 01 Bacon Smith 02 01 White Con 02 01 Funk Rudd 03 02 Koch 01 Ota Akhtar 03 02 Barr 01 Funk Smith 02 Smith White 03 03 02 Jones Funk 03 02 White Ota 03 Martin 04 03 Barr Smith 03 Jones 04 Martin Finding Rows in a Heap with a Nonclustered Index sysindexes id indid 2 root Akhtar Martin SELECT lastname firstname Page 37 Page 28 Page 12 Root AkhtarFROM member Martin Ganio Smith WHERE lastname Page 41 Akhtar 4 706 01 Barr 4 705 03 Con 4 704 01 Funk 4 706 02 Funk 4 704 02 Non Leaf Level Non Clustered clustered Index BETWEEN Masters AND Rudd Page 51 Ganio 4 709 01 Hall 4 709 04 Jones 4 709 02 Jones 4 708 03 Jones 4 707 03 Page 61 Martin 4 708 01 Matey 4 706 04 Matey Ota 4 707 02 Ota Phua 4 708 02 Rudd 4 705 01 Page 71 Leaf Level Smith 4 706 03 Key Value Smith 4 708 04 Smith 4 707 01 White 4 704 03 White 4 705 02 Heap Page 704 01 Conn 02 Funk 03 White File ID 4 Page 705 01 Rudd 02 White 03 Barr Page 706 01 Akhtar 02 Funk 03 Smith 04 Matey Page 707 01 Smith 02 Ota 02 03 Jones Page 708 01 Martin 02 02 Phua 03 Jones 04 Smith Page 709 01 Ganio 02 Jones 03 Hall Finding Rows in a Clustered Index sysindexes id indid 1 root Clustered Index Akhtar Martin Page 140 Root Akhtar SELECT lastname firstname Martin Ganio Smith FROM member WHERE lastname Ota Page 141 Page 145 Akhtar 2334 Barr 5678 Con 2534 Funk 1334 Funk 1534 Page 100 Ganio 7678 Hall 8078 Jones 2434 Jones 5978 Jones 2634 Page 110 Martin 1234 Martin 7778 Ota 5878 Phua 7878 Rudd 6078 Page 120 Smith 1434 Smith 5778 Smith 7978 White 2234 White 1634 Page 130 Finding Rows in a Clustered Index with a Nonclustered Index sysindexes Nonclustered Index on First Name id indid 2 root Non Leaf Level Aaron Jose Aaron Jose SELECT lastname firstname phone Deanna Nina FROM member WHERE firstname Mike Aaron Adam Amie Con Barr Baldwin Daum Hall Hampton Jose Judy Mike Leaf Level Lugo Clustered Kaethler Key Value Nash Barr Kim Nagata O Melia Clustered Index On Last Name Barr Cox Daum Deanna Don Doug Adam Arlette Deanna Kim Kobara LaBrie Shane Linda Ryan Nagata Nash Nixon Susanne Mike Toby How SQL Server Maintains Index and Heap Structures Page Splits in an Index Forwarding Pointer in a Heap How SQL Server Updates Rows How SQL Server Deletes Rows Page Splits in an Index INSERT member last name VALUES lastname Jackson Index Pages Non Leaf Level Akhtar Martin Akhtar Ganio Jackson Lang Smith Leaf Leaf Level Level Key Value Akhtar Barr Barr Borm Buhl Ganio Akhtar Hall Barr Hart Barr Borm Buhl Jackson Ganio Jones Hall Jones Hart Jones Jones Lang Martin Martin Martin Moris Smith Smith Smith Smith Smith Forwarding Pointer in a Heap sysindexes Page 37 Akhtar Ganio UPDATE id indid 2 root Akhtar Martin Page 12 Root Non Leaf Level Page 28 Martin Smith Non Clustered clustered Index member SET Page Address Page something long Leaf Level Page 41 51 61 Page 71 WHERE 4 708 01 Ota Smith 4 706 03 Key Value Akhtar 4 706 01 Ganiolastname 4 709 01 Martin Barr Con Funk Funk 4 705 03 4 704 01 4 706 02 4 704 02 Hall Jones Jones Jones 4 709 04 4 709 02 4 708 03 4 707 03 Martin Ota Ota Phua Rudd 4 706 04 …