Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

SQL Server - In Clustered index, Rows in a page will not store in order. Slot array only will be in order

Aug 21 2011 2:55AM by Ramireddy   

I always thought that for Clustered Index, Rows physically will store in a page in order. In our recent Chennai UG meet, speaker Ramkumar explained paul randall myths. He mentioned that records are not stored in order, only slot array will be in order. At first glance, i was surprised. But here is the script, which proves that. You can see that in DBCC page output, Slot Array indicating order of rows.

create table TestInserts(Col varchar(100) primary key)
insert into TestInserts values ('a')
insert into TestInserts values ('c')
insert into TestInserts values ('b')

select object_ID('TestInserts')
-- Take the objectid returned from above query and keep it as second argument in below query
dbcc ind(7,1625498757,1,1)
GO
-- In above result, take the 1st page in index level 0 and use it as 3rd argument in dbcc page command. First argument is the DBId
dbcc traceon(3604)
dbcc page(7,1,327,2)
Read More..   [0 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Ramireddy
2 · 40% · 12972
11
 
4
 
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

5  Comments  

  • Hi Rami,

    Can you please elaborate more on this problem? What are these DBCC commands?

    Thanks in advance.

    commented on Aug 22 2011 9:42PM
    Naomi
    31 · 6% · 1776
  • Hi Rami, Nice article.

    An excerpt from MSDN: When you create a clustered index, SQL Server reorganizes the data pages so that the rows are logically stored in clustered-index order. SQL Server doesn't necessarily store the data physically on the disk in clustered-index order, but while creating an index, SQL Server attempts to physically order the data as close to the logical order as possible. Each page in an index's leaf level has a pointer to the page that logically precedes the current page and to the page that logically follows the current page, thereby creating a doubly linked list. The sysindexes table contains the address of the first leaf-level page. Because the data is guaranteed to be logically in clustered-index order, SQL Server can just start at the first page and follow the index pointers from one page to the next to retrieve the data in order.

    check this link: http://msdn.microsoft.com/en-us/library/aa496062%28v=sql.80%29.aspx

    commented on Aug 23 2011 12:14AM
    Manoj
    240 · 1% · 188
  • Naomi,

    Here, I am creating a clustered index on a table and inserting records "a","c","b". As its clustered index, we might assume that it will store the records in page also in order. So, page contents might be "a","b","c" . However its not true. In page, it will store it as "a","c","b" only. Every page will have slot array at the end indicating the position of the records in the page. Only slot array will be in order. I am proving that by looking into page contents.

    DBCC IND : This will returns the pages of index
    DBCC Page : By using this we can see the physical content of page.
    
    Lines 1- 4  I was creating a table and inserting some rows. 
    Line 6 :  Finding objectid of table, which will be used in DBCC IND statement in next line
    Line 8 : It will returns the pages of the table. As we inserted only 3 records, it will return only 2 pages. One is IAM page and another one is Level0 page. Take that PageID
    Line 11: To see Page output, we need to on 3604 trace flag. 
    Line 12: DBCC Page : this will show page contents
    

    If you observe page contents, in the end it will shows slot array. If you observe slot array, Slot0 and Slot1 and Slot 2 represents the "a","b","c" having address some thing like 96,120,108. It means, records are not stored physically in order within page.

    commented on Aug 23 2011 12:51AM
    Ramireddy
    2 · 40% · 12972
  • Thanks for sharing the links, Vishal. They are really good resources.

    commented on Sep 3 2011 12:28AM
    Nakul Vachhrajani
    4 · 36% · 11635

Your Comment


Sign Up or Login to post a comment.

"SQL Server - In Clustered index, Rows in a page will not store in order. Slot array only will be in order " rated 5 out of 5 by 11 readers
SQL Server - In Clustered index, Rows in a page will not store in order. Slot array only will be in order , 5.0 out of 5 based on 11 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]