MSSQL DB Index Doesn't indexing my table with given index rule

i have database tables which has custom index. When i run query for this table i realised my table result doesn’t returns values as indexed.

Here Is My User Table

I have index for CreateDate in User Table

1

When i run this query SELECT [Id], [IsDeleted], [Login], [CreateDate], [DeleteDate] FROM [Users]

I’m getting output like this :

2

As you see result don’t sorted with createdate.

But however when i try to use this query SELECT [Id],[CreateDate] FROM [Users]

I’m getting sorted output:

3

Is it normal to give query output like this (didn’t sorted with index)? Is it effects performance in any case ?

>Solution :

You should never assume anything about the order of the results of a query unless you explicitly specify an order by clause.

Without it, the RDBMS is free to return rows in whatever way it "sees" fit, and it may change if something falls out of the cache, the order on the disk changes due to rebuilding the index or any number of other external circumstances.

Leave a Reply