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 indid Object Object Type Type 00 Heap Heap 11 Clustered Clustered Index Index 22 to to 250 250 Nonclustered Nonclustered Index Index 255 255 text text ntext ntext or or image 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 Martin 03 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 Non Leaf Level Martin SELECT lastname firstname Page 37SELECT lastname Page 28 firstname Page 12 Root AkhtarFROM Martin FROM member member Ganio Smith WHERE lastname 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 Clustered clustered Index BETWEEN Masters AND Masters AND71 Rudd Rudd Page 61 Page Leaf Level BETWEEN Page 51 Ganio 4 709 01 Hall 4 709 04 Jones 4 709 02 Jones 4 708 03 Jones 4 707 03 Martin Matey Matey Ota Ota Phua Phua Rudd 4 708 01 4 706 04 4 707 02 4 708 02 4 705 01 Smith Smith Smith White White 4 706 03 Key Value 4 708 04 4 707 01 4 704 03 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 02 Ota 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 Martin Page 140 Root Akhtar Martin Martin SELECT lastname firstname SELECT lastname firstname Ganio Smith FROM member FROM member WHERE lastname Ota WHERE Page 141 lastname Ota 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 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 phone SELECT lastname firstname firstname phone Deanna Nina FROM member FROM member WHERE WHERE firstname firstname Mike Mike Aaron Adam Amie Con Barr Baldwin Daum Hall Hampton Jose Judy Mike Mike Leaf Level Lugo Clustered Kaethler Key Value Nash Nash Barr Kim Nagata 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 Nash Nixon Susanne Mike 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 INSERT member member last last name name VALUES lastname Jackson 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 UPDATE id indid 2 Akhtar Martin Page 12 Root root Non Leaf Level Page 28 Martin Smith Non Clustered clustered Index member member SET Address Page something long SETPage Address something long Leaf Level Page 41 51 …