Clustered Index should not be used for Sorting


Howdy Everyone…

It is a common misconception that writing a clustered index on a table will get the table sorted on the index key columns. Although this is true for some extent, there are some exceptions to it, more over it is not documented and not supported by Microsoft, so they can remove this functionality without even letting you know about it (and not support it in the future versions of SQL server).

The below SQL script is a perfect example where we can see on why we should use order by clause every time.

use tempdb

create table emp

(

id  INT IDENTITY(1,1) PRIMARY KEY

,name varchar(200) NULL

)

GO

 

select object_name(object_id) table_name,name,type_desc  From sys.indexes where  object_id = object_id('emp')

table_name name type_desc
emp PK__emp__5FFB4DF9 CLUSTERED

insert into emp (name) values ('raj')

insert into emp (name) values ('suri')

insert into emp (name) values ('siva')

insert into emp (name) values ('murali')

 

select * from emp

id name

1

raj

2

suri

3

siva

4

murali

create unique index uix_empName ON emp (name)

select object_name(object_id) table_name,name,type_desc From sys.indexes where  object_id = object_id('emp')

table_name name type_desc
emp PK__emp__5FFB4DF9 CLUSTERED
emp uix_empName NONCLUSTERED

select * from emp

id name

4

murali

1

raj

3

siva

2

suri

As you can see the second select statement resulted in different order of rows even though there is a clustered index on the table.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s