How is data arranged when a clustered index is created?

Question

How is data arranged when a clustered index is created?

sequelgarrett

SSC Eights!

Points: 816
More actions
June 28, 2011 at 2:33 pm

#240051

I am trying to understand indexes better, how is the order of data is determined when a clustered index is created on a table?
I thought it would be in sequential order (assuming an ascending index is created), but that doesn't always seem to be the case.
Why is the data in order in the first case, but not in the second when more rows are stored in the table?
If anybody could help explain this, I'd appreciate it. Thanks!
select top 50000
id = identity(int,1,1)
into #Ordered
from sys.columns a, sys.columns b
select top 75000
id = identity(int,1,1)
into #NotOrdered
from sys.columns a, sys.columns b
create clustered index cix on #Ordered (id asc)
create clustered index cix on #NotOrdered (id asc)
select *
from #Ordered
select *
from #NotOrdered

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Tables are by definition unordered. What you're probably seeing is not a difference in the way the data is stored, but the way SQL's returning it.

SQL will try to put a new index down so that the logical and the physical order of the pages are the same. There must be enough space in the data file for it to do that. However the way the data is physically stored and the way it's returned by a query may not be the same.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323504 More actions · Answer 2

there is no default order by.

without an ORDER BY , ther eis no guarantee the data is returned in any order.

sometimes, by coincidence, the data is returned in the order of the clustered index....

if you add a clustered index AFTER teh table was populated, SQL may just create the index to get to teh data, but not necessarily use it to get the data in that particular order.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!