January 6, 2009 at 11:47 am
kathyoshea (1/6/2009)
Create a new multi-statement table-valued function.Select the data and use the order by in the loading of the table that is returned.
This works in all the testing I've done.
That's still not guaranteed to work. In SQL, there is no guarantee that order inserted = order returned. In many cases, with small tables and simple queries, that will happen. It doesn't mean it will happen in all cases everywhere, every time.
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
January 6, 2009 at 12:12 pm
I might be missing something, but how about creating a sorting key when populating the table and the selecting from table order by sortkey...
January 6, 2009 at 1:25 pm
J (1/6/2009)
I might be missing something, but how about creating a sorting key when populating the table and the selecting from table order by sortkey...
If you're going to add an Order By to it, why use a special column for that, when you probably already have one? The problem here is someone trying to get the order directly from the function, instead of in an outer query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2009 at 2:10 pm
instead of in an outer query
Yup. I drifted away from the original question.
January 6, 2009 at 2:17 pm
J (1/6/2009)
instead of in an outer queryYup. I drifted away from the original question.
Because if it's not in the outermost query, you can't guarantee it will work. Might, might not. I don't like relying on luck in queries.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 6, 2009 at 7:34 pm
If all the data being fetched was in a covering index, would that guarantee the order?
Todd Fifield
January 6, 2009 at 7:51 pm
tfifield (1/6/2009)
If all the data being fetched was in a covering index, would that guarantee the order?Todd Fifield
Not with a SELECT. I've got a code example around here somewhere that shows that even a single column table with a clustered index on it can still give you the wrong order even if you use and index "hint"... I'm trying to find it.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2009 at 12:02 am
tfifield (1/6/2009)
If all the data being fetched was in a covering index, would that guarantee the order?Todd Fifield
Once more with feeling...
The only way to absolutely guarantee an order is to use an order by clause. All other hacks and tricks may work under specific conditions in the current versions of SQL and may change at any time if the underlying implementation changes.
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
January 7, 2009 at 12:42 am
Once again a UDF got you in troubles because of hidden joins !
You should really rework that to proper joins in you calling queries and get control over you queries.
However there may be a TEMPORARY bypass by using the startup parameter that has been provided for the "views with order by nolonger work" problem.
http://support.microsoft.com/kb/926292
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 7, 2009 at 12:56 am
ALZDBA (1/7/2009)
However there may be a TEMPORARY bypass by using the startup parameter that has been provided for the "views with order by nolonger work" problem.
With one caveat:
From the kb article:
Note Trace flag 168 must be set before the database is migrated to SQL Server 2005. If trace flag 168 is set after the database is migrated, the query result will remain unsorted.
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
January 7, 2009 at 1:04 am
GilaMonster (1/7/2009)
ALZDBA (1/7/2009)
However there may be a TEMPORARY bypass by using the startup parameter that has been provided for the "views with order by nolonger work" problem.
With one caveat:
From the kb article:
Note Trace flag 168 must be set before the database is migrated to SQL Server 2005. If trace flag 168 is set after the database is migrated, the query result will remain unsorted.
Darn little notes :hehe:
The OP could still test it....
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply