Sql-server – Clustered index and Non clustered index

indexsql server

This might be a silly question but i really want to know the internals.This is one of small table just to test as below

enter image description here

I have create a table as above and 'name' column is primary key and clustered index so it has sort all words in alphabetical order . Now when i create a non clustered index on column 'number' , the rows in 'name' column not anymore in alphabetical order as below

enter image description here

My question is, the name column should remain the rows in alphabetical order rite since it has clustered index and how come it changed after i add non clustered index on different column? i m sure there might be certain things i missed.Please let me know.Thank you

Best Answer

Order of your data returned is not guaranteed when you do not specify an order by.

Consider this example:

CREATE TABLE dbo.SmallTable([name] varchar(25) PRIMARY KEY NOT NULL,
                            Number int);

INSERT INTO dbo.SmallTable ([name],[Number]) VALUES ('compi',15 ), ('jack',5 ), ('malik',20 ), ('nana',10 );

If you were to just select without an order by:

SELECT [name],[Number] 
FROM dbo.SmallTable;

The order this time is the same as your first example:

name    Number
compi   15
jack    5
malik   20
nana    10

But this is not guaranteed.

If you want the ordering to be guaranteed then you need to add an order by:

SELECT [name],[Number] 
FROM dbo.SmallTable
ORDER BY [name] ASC;

What you are seeing when creating the nonclustered index on number is the query using this new NC index.

For example:

CREATE INDEX IX_Number
on dbo.SmallTable(Number);

Running the query again:

SELECT [name],[Number] 
FROM dbo.SmallTable;

I get the same result as you:

name    Number
jack    5
nana    10
compi   15
malik   20

Because now sql server decides to use the IX_Number index to return the results:

enter image description here

Which is ordered by number asc.

To guarantee the ordering the query needs to be changed to:

SELECT [name],[Number] 
FROM dbo.SmallTable;
ORDER BY [name] asc;

The ordering could even be different than the index order from the index that was used. This means that even if now the ordering is on the number or name column, it could be on any one of these columns or even none.

Always use an order by if the ordering is important.

More info on the subject can be found here, here, here and here

Related Question