1 / 8

How SQL Server Stores and Accesses Data

Con. Rudd. Akhtar. Smith. Martin. Ganio. Funk. White. Funk. Ota. Phua. Jones. White. Barr. Smith. Jones. Jones. Hall. Martin. Smith. 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

tanek
Download Presentation

How SQL Server Stores and Accesses Data

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Con ... ... ... ... ... Rudd Akhtar Smith Martin Ganio ... Funk ... ... ... ... ... White Funk Ota Phua Jones ... White ... ... ... ... ... Barr Smith Jones Jones Hall ... ... ... ... ... ... ... ... Martin ... Smith ... ... ... ... ... ... ... ... ... ... ... ... ... ... 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 8 Page 9 Page 4 Page 5 Page 6 Page 7

  2. 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

  3. 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

  4. Using Nonclustered Indexes • Nonclustered Indexes Are the SQL Server Default • Existing Nonclustered Indexes Are Automatically Rebuilt When: • An existing clustered index is dropped • A clustered index is created • The DROP_EXISTING option is used to change which columns define the clustered index

  5. sysindexes id indid = 0 First IAM Extent Bit Map … 127 1 01 01 01 01 01 01 01 Dunn Graff Con Con Con Con Con … … … … … … … 128 1 02 02 02 02 02 02 02 Funk Funk Funk Funk Randall Bacon Funk … … … … … … … 129 0 03 03 03 03 03 03 03 Ota White White Koch White White White … … … … … … … 130 1 04 … … … … … 04 ... ... ... Durkin ... ... Slichter ... ... ... ... ... ... ... … 05 … 05 … … … … ... ... Lang LaBrie ... ... ... ... ... ... ... ... ... ... 01 Seattle … 02 Paris … 03 Tokyo … 01 01 01 01 01 01 01 01 Rudd Rudd Rudd Rudd Rudd Rudd Rudd Rudd … … … … … … … … 01 01 01 01 01 01 01 01 Akhtar Akhtar Akhtar Akhtar Akhtar Akhtar Akhtar Akhtar … … … … … … … … 01 01 01 01 01 01 01 01 Smith Smith Smith Smith Smith Smith Smith Smith … … … … … … … … 04 Atlanta ... 02 02 02 02 02 02 02 02 White White White White White White White White … … … … … … … … 02 02 02 02 02 02 02 02 Funk Funk Funk Funk Funk Funk Funk Funk … … … … … … … … 02 02 02 02 02 02 02 02 Ota Ota Ota Ota Ota Ota Ota Ota … … … … … … … … … ... ... 03 03 03 03 03 03 03 03 Barr Barr Barr Barr Barr Barr Barr Barr … … … … … … … … 03 03 03 03 03 03 03 03 Smith Smith Smith Smith Smith Smith Smith Smith … … … … … … … … 03 03 03 03 03 03 03 03 Jones Jones Jones Jones Jones Jones Jones Jones … … … … … … … … … … … … … … … … ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... 04 04 04 04 04 04 04 04 Martin Martin Martin Martin Martin Martin Martin Martin ... ... ... ... ... ... ... ... … … … … … … … … … … … … … … … … ... ... ... ... ... ... ... ... … … … … … … … … ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... … … … … … … … … ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... … … … … … … … … ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... Finding Rows Without Indexes IAM Heap Extent 128 Extent 129 Extent 130 Extent 127

  6. id indid = 2 root Non clustered Index Non Clustered Index Non-Leaf Level Non-Leaf Level Akhtar Akhtar ... ... Martin Martin Martin Akhtar Akhtar Page 37 Page 37 Page 28 Page 28 Page 12 - Root Page 12 - Root Ganio Ganio Martin Martin Martin ... ... Smith Smith ... ... Leaf Level(Key Value) Leaf Level(Key Value) Page 41 Page 41 Page 51 Page 51 Page 61 Page 61 Page 71 Page 71 Akhtar Akhtar 4:706:01 4:706:01 Smith Ganio Ganio Smith 4:709:01 4:709:01 4:706:03 4:706:03 Barr Barr 4:705:03 4:705:03 Hall Hall Smith Smith 4:709:04 4:708:04 4:709:04 4:708:04 Martin Martin 4:708:01 4:708:01 Con Con 4:704:01 4:704:01 Smith Jones Smith Jones 4:709:02 4:707:01 4:707:01 4:709:02 Matey Matey Matey 4:706:04 4:706:04 4:706:04 Funk Funk 4:706:02 4:706:02 White Jones Jones White 4:704:03 4:704:03 4:708:03 4:708:03 Ota Ota 4:707:02 4:707:02 Funk Funk 4:704:02 4:704:02 White Jones White Jones 4:707:03 4:705:02 4:707:03 4:705:02 Ota Phua Phua Phua 4:707:02 4:708:02 4:708:02 4:708:02 Rudd Rudd Rudd 4:705:01 4:705:01 4:705:01 Heap Heap Page 704 Page 704 Page 705 Page 705 Page 706 Page 706 Page 707 Page 707 Page 808 Page 808 Page 709 Page 709 01 01 ... ... Akhtar Akhtar 01 01 ... ... Conn Conn 01 01 01 ... ... ... Rudd Rudd Rudd 01 01 ... ... Smith Smith 01 01 ... ... Martin Martin 01 01 ... ... Ganio Ganio 02 02 ... ... Funk Funk 02 02 ... ... Funk Funk 02 02 ... ... White White 02 02 ... ... Ota Ota 02 02 02 02 ... ... ... ... Phua Ota Phua Phua 02 02 ... ... Jones Jones 03 03 ... ... Smith Smith 03 03 ... ... White White 03 03 ... ... Barr Barr 03 03 ... ... Jones Jones 03 03 ... ... Jones Jones 03 03 ... ... Hall Hall 04 04 ... ... Matey Matey ... ... ... ... ... ... ... ... ... ... ... ... 04 ... Matey ... ... ... ... ... ... 04 04 ... ... Smith Smith ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... File ID #4 File ID #4 Finding Rows in a Heap with a Nonclustered Index sysindexes SELECT lastname, firstname FROM member WHERE lastname BETWEEN 'Masters' AND 'Rudd'

  7. id indid = 1 root sysindexes Clustered Index Clustered Index Akhtar Akhtar … … Martin Martin Page 140 - Root Page 140 - Root Akhtar Akhtar Martin Martin Ganio Ganio Smith Smith … … … … Page 141 Page 141 Page 145 Page 145 Akhtar Akhtar 2334 2334 ... ... Ganio Ganio 7678 7678 ... ... Martin Martin 1234 1234 ... ... Smith Smith 1434 1434 ... ... Barr Barr 5678 5678 ... ... Hall Hall 8078 8078 ... ... Martin Martin 7778 7778 ... ... Smith Smith 5778 5778 ... ... Con Con 2534 2534 ... ... Jones Jones 2434 2434 ... ... Ota Ota 5878 5878 ... ... Smith Smith 7978 7978 ... ... Funk Funk 1334 1334 ... ... Jones Jones 5978 5978 ... ... Phua Phua 7878 7878 ... ... White White 2234 2234 ... ... ... ... ... ... ... ... ... ... Funk Funk 1534 1534 Jones Jones 2634 2634 Rudd Rudd 6078 6078 White White 1634 1634 ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... Page 100 Page 100 Page 110 Page 110 Page 120 Page 120 Page 130 Page 130 Ota 5878 ... Finding Rows in a Clustered Index Martin SELECT lastname, firstname FROM member WHERE lastname = 'Ota' Martin

  8. id indid = 2 root Nonclustered Index on First Name Nonclustered Index on First Name Non-Leaf Level Non-Leaf Level Aaron Aaron ... ... Jose Jose Aaron Aaron Jose Jose Deanna Deanna Nina Nina … … … … Leaf Level(Clustered Key Value) Leaf Level(Clustered Key Value) Aaron Aaron Con Con Jose Jose Lugo Lugo Deanna Deanna Daum Daum Adam Adam Barr Barr Judy Judy Kaethler Kaethler Don Don Hall Hall Amie Amie Baldwin Baldwin Mike Mike Mike Nash Nash Nash Doug Doug Hampton Hampton … … … … … … … … … … … … Barr Barr Clustered Index On Last Name Clustered Index On Last Name Kim Kim Nagata Nagata O’Melia O’Melia Barr Barr Adam Adam … … Kim Kim Shane Shane … … Nagata Nagata Susanne Susanne … … Cox Cox Arlette Arlette … … Kobara Kobara Linda Linda … … Nash Nash Nash Mike Mike Mike … … … Daum Daum Deanna Deanna … … LaBrie LaBrie Ryan Ryan … … Nixon Nixon Toby Toby … … … … … … … … … … … … … … … … … … … … Finding Rows in a Clustered Index with a Nonclustered Index sysindexes SELECT lastname, firstname, phone FROM member WHERE firstname = 'Mike' Nagata

More Related