September 6, 2011 at 2:05 am
For ex, I have a table employee with empid,name and salary as column and I ccreate a clustered index on salary column. Now If I want to retreive employees with least 5 salaries and use a TOP 5 operator in my select query than do I need to put an ORDER BY also? And Is it sure that using a TOP without an ORDER BY will always give consistent results?
How would it impact the performance?
September 6, 2011 at 2:11 am
Select TOP X just limits the result set to the top X records; so if you sort by salary desc; then selecting the top 5 would give you your desired result.
I dont believe there is any more or less penalty on the index lookup as it would need to look for the data regardless.
HTH
September 6, 2011 at 2:13 am
Yes, but I already have a clustered index on my salary column so do I need any external sorting as well?
September 6, 2011 at 2:16 am
kirti.malukani (9/6/2011)
Yes, but I already have a clustered index on my salary column so do I need any external sorting as well?
No; The clustered index physically sorts the data in order when it's written to the table (assuming the cl.idx existed at the time of insert) or as the cl.idx is being created.
HTH
September 6, 2011 at 2:16 am
Sorting will not be 100% ALWAYS unless you provide with an ORDER by.
September 6, 2011 at 2:31 am
kirti.malukani (9/6/2011)
Yes, but I already have a clustered index on my salary column so do I need any external sorting as well?
Yes, yes, yes , absolutley. If you want rows back in a specific order, you HAVE TO specify ORDER BY.
I blogged about this a while back,
September 6, 2011 at 2:32 am
kirti.malukani (9/6/2011)
Yes, but I already have a clustered index on my salary column so do I need any external sorting as well?
Yes you will also need an order by, the only way to guarantee the order of a query is to use ORDER BY, a clustered index does not always return the correct order.
September 6, 2011 at 2:33 am
Apologies; The above posters are correct - I mis-read your comment...
September 6, 2011 at 4:10 am
St3veMax (9/6/2011)
kirti.malukani (9/6/2011)
Yes, but I already have a clustered index on my salary column so do I need any external sorting as well?No; The clustered index physically sorts the data in order when it's written to the table (assuming the cl.idx existed at the time of insert) or as the cl.idx is being created.
No it doesn't. It provides a logical ordering of the index data within the file, not necessarily a physical ordering. Regardless of that, no order by means no guarantee of order. End of story.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply