Non Unique Non-Clustered index sort order

Question

Hi,

I had a question on the sort order for a non-unique non-clustered index. Say if i have a non-unique, non-clustered index on the Employees table(Clustered index is unique on EmpID) and there are hundred employees with SMITH as the last name, How does Sql Server sort this 100 SMITH records in the non-clustered index?  I am thinking for a non-unique non-clustered index , if it considers the clustered index as well  for sorting  when there are more than 1 record with the same key value?

Happy to be corrected/clarified.

Thanks

Answer 1

What do you mean by sort? Only ORDER BY clause guarantee a proper sorting . 

If the NCI does not cover the query  it  will traverse thru the B-Tee of  the NCI  getting to 

the leaf level  and then goes to Clustretered index B-Tree to the get  the data

If it does  the data will be return from NCI btree

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

sorry , i should have been clearer. what i meant was if there are 100 records with SMITH last name, how are these 100 records stored  within the B tree of the non-clustered index.  which SMITH record will be first, second , third etc within the Non-clustered index?

Answer 3

It will be logically sorted by clustered index key, so if you have empid=1 Smith and empid=10 Smith

and have CI on empid, so data will be stored on the pages sorted by empid

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 4

have CI on empid, so data will be stored on the pages sorted by empid

Uri, also on a CI we have index fragmentation and that means the pages are completly in physical order of the CI.

As you already correctly wrote, only ORDER BY clause guarantee a proper sorting

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 5

Uri, also on a CI we have index fragmentation and that means the pages are completly in physical order of the CI.

Not sure what you mean here. As far as I can recall, in the non-clustered index, those 100 SMITH will appear in the order of the row locator. The CI may be fragmented, but that does not affect the order in the the non-clustered index.

As you already correctly wrote, only ORDER BY clause guarantee a proper sorting

Yes. If you only say ORDER BY EmpName and you by some quirk of fate get rows in different order than the CI key, this is not a bug, but a perfectly correct result.

Answer 6

It will be logically sorted by clustered index key, so if you have empid=1 Smith and empid=10 Smith and have CI on empid, so data will be stored on the pages sorted by empid

The rows on the clustered pages will be stored in that order, but I don't think that was the question, the question was about the 100 Smith entries in the LastName nonclustered index where I would be mildly surprised if there is anything but random order.

(and as pointed out, unless you also specify ORDER BY which I think will almost always cause SQL Server to do the sort to guaranty things, you are not *guaranteed* any order at all)

Josh

Answer 7

A question was how 

< i meant was if there are 100 records with SMITH last name, how are these 100 records stored >

I>>> would be mildly surprised if there is anything but random order.

Because of the clustered index on empid ,sure if there is  a last name Smith employee  with empid=10  and amonther Smith

with empid=1000 , so sure  they would NOT store on the same page :-)))))

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence