June 6, 2012 at 11:26 am
I am working on a performance project and one of the people I work with is not comfortable adding a non clustered index because they are concerned it will affect the order in which data is returned from a table. Yes I know I can use and order by statement and I have, but...
I need some backup on this:
Do non clustered indexes affect the order in which data is returned from a table if no order by clause is used.
I've been googling and can't find any technical documentation regarding this.
thanks
always get a backup before you try that.
June 6, 2012 at 11:32 am
There are lots of things that can change the order in which data is retrieved. The only way to guarantee a certain retrieval order is with an 'order by' clause. If that clause is not present, then the query engine can, and will, bring the data back in any order it finds convenient at the time.
If you're counting on a particular order without having an 'order by' in the sql, then you're already in trouble.
So yes, adding an index MIGHT cause a change, applying a service pack MIGHT cause a change, a change in data distribution or disk placement MIGHT cause a change.
Add the 'order by' if you really need a specific order.
June 6, 2012 at 11:37 am
yes we have the order by statements. Not sure why the reluctance other than in the past the other non dba developer relied on static data to be in some tables in a particular order without including any kind of sequencing device.
nothing more fun that working on legacy software
always get a backup before you try that.
June 6, 2012 at 11:39 am
David Webb-200187 (6/6/2012)
There are lots of things that can change the order in which data is retrieved. The only way to guarantee a certain retrieval order is with an 'order by' clause. If that clause is not present, then the query engine can, and will, bring the data back in any order it finds convenient at the time.If you're counting on a particular order without having an 'order by' in the sql, then you're already in trouble.
So yes, adding an index MIGHT cause a change, applying a service pack MIGHT cause a change, a change in data distribution or disk placement MIGHT cause a change.
Add the 'order by' if you really need a specific order.
+1. Fully agree.
Edit post: Bit by the quote bug, and I wanted the correct quote here.
June 6, 2012 at 12:15 pm
Mountain Steve (6/6/2012)
yes we have the order by statements. Not sure why the reluctance other than in the past the other non dba developer relied on static data to be in some tables in a particular order without including any kind of sequencing device.nothing more fun that working on legacy software
Agreed - you still have a timebomb going on. All it could take is for someone to pull that table's data in a completely different order (using an ORDER by to do so), and SQL Server may use the data retrieved and sitting in memory to serve up your next query.
Ugh - never fun to deal with those
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 6, 2012 at 12:32 pm
Well it took an act of God for to get a sequence number on one particular table and we were depending on BCP to keep the data in the correct order. The column was a list of rule names that had to be in a date added sort order and not an 0-1 a-z order. If we used a sequence number/date added column it would solve it.
so five years later with bloody knuckles from trying to keep it bcp'd in the correct order we finally changed it to include sequencing
sigh
when is the economy going to improve?
always get a backup before you try that.
June 6, 2012 at 12:39 pm
Anything that changes the execution plan (parallelism, joins, etc) or changes the locks could result in a different order unless you have an ORDER BY.
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 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply