Display Order due to NonClustered Index

Question

Post reply

Display Order due to NonClustered Index

er.mayankshukla

SSCarpal Tunnel

Points: 4190
More actions
December 30, 2014 at 12:22 am

#293265

I need a column to be displayed in sequence but I already had a clustered index on other column of that Table.
So I created a non clustered index on that Column and I am getting the results in order.
Let me give one example:
create table dbo.TestIdx
(
CacheId bigint,
SequenceNumber int
)
create clustered index cix_test on TestIdx(CacheId asc)
create nonclustered index nix_test on TestIdx(CacheId asc,SequenceNumber asc)
insert into TestIdx(CacheId,SequenceNumber)
values (3,1),(3,2),(1,4),(1,3),(1,1),(1,2),(2,2),(2,1)
select * From TestIdx
When I am displaying, I am getting the ascending order of CacheId and SequenceNumber.
I am not sure how this result I am getting.
After checking the execution plan I see that Select clause is using the NonClustered Index that's why I am getting that Order.
Need some suggestions on this

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 1

Only ORDER BY Clause can guarantee the ordered display of records, index cannot guarantee ResultSet display.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

No ORDER BY, no guarantee of order. End of Story

If you need a particular order, put an ORDER BY on your query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Shafat Husain SSCrazy Points: 2754 More actions · Answer 3

GilaMonster (12/30/2014)
No ORDER BY, no guarantee of order. End of Story
If you need a particular order, put an ORDER BY on your query.

Why so serious...!! :crying:

Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Shafat Husain (12/30/2014)
GilaMonster (12/30/2014)
No ORDER BY, no guarantee of order. End of Story
If you need a particular order, put an ORDER BY on your query.
Why so serious...!! :crying:

You get it right, or you get it wrong. How seriously do you take the company who pays your salary?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden