Clustered Index Question [70-431 MCTS SQL Svr 2005]

RobertKaucher

I have a question about something I just read in SQL Server 2005 Unleashed by Rankins et al. On page 972 while disscussing clustered indeces it is stated that "...the rows on each page might not be physically sorted in clustered index order, depending on when rows were inserted or deleted in the page."

Is this ordering the "collation method" set at the installation of SQL Server?

Thanks!

Goldmember

As far as I know, the collation has to do with the language and character set used to store data.

For example, if you choose Swedish-Latin collation you will store data in that particular mode.


So my guess would be collation has nothing to do with your problem.

I don't know the answer, only that collation won't be the issue.


Good luck, maybe Beaver32 can help

Goldmember

astorrs

Hmm, as I read it, it sounds to me like the SQL Books Online disagrees with your book.

The pages in the data chain and the rows in them are ordered on the value of the clustered index key. All inserts are made at the point where the key value in the inserted row fits in the ordering sequence among existing rows.

http://msdn.microsoft.com/en-us/library/ms177443.aspx

Now you've got me curious. Can someone with more depth in SQL provide some insight? Wouldn't it as described in the Rankins' book work against the whole idea of a clustered index?

-- UPDATE --
I misread "sorted" as "stored" in the original post - hence my confusion.

RobertKaucher

Goldmember wrote:

As far as I know, the collation has to do with the language and character set used to store data.

For example, if you choose Swedish-Latin collation you will store data in that particular mode.


So my guess would be collation has nothing to do with your problem.

Yes, that's half the story. It is not only how the data is stored but how it is sorted. What I would like to know is if the collation method defined at install is what determines how data is sorted in a clustered index. It only seems logical to me that it would.

astorrs

I agree it should store it in the default collation. Your table could have different collations on each column (i.e., different collations on different parts of your clustered-index key), so I don't see how the clustered index could be sorted based on anything but the default.

RobertKaucher

astorrs wrote:

Now you've got me curious. Can someone with more depth in SQL provide some insight? Wouldn't it as described in the Rankins' book work against the whole idea of a clustered index?

Not really. Because SQL is not told exactly where the data is found it is only told where to go look for it. If I query for Bill, for example, the index doesn't tell SQL "bill" is in location X, it simply says the b's begin at x and end at y go look there, correct? It doesn't matter if the "leaf data" is mixed because SQL will have to look through all of the b's to ensure there aren't multiple matches. Of course if the index is rebuilt it will be sorted "properly." I think BoL is taking an idealistic look, but I am really not sure.

Hope some SQL guru will be able to solve that question for us.