Upgraded from 2000 to 2005; now some views not working correctly

Question

Upgraded from 2000 to 2005; now some views not working correctly

WayneS

SSC Guru

Points: 95460
More actions
August 31, 2008 at 3:40 pm

#192126

I just upgraded a sql server 2000 instance to 2005.
One of the databases has some views that use :
select top 100 percent <columns to retrieve)
order by columna, etc.
the view is returning the correct data, but the data isn't being ordered.
If I run the select statement directly, it does work correctly.
Any idea why the views have stopped ordering the data?
Thanks,
Wayne
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Viewing 3 posts - 1 through 2 (of 2 total)

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

WayneS (8/31/2008)
I just upgraded a sql server 2000 instance to 2005.
One of the databases has some views that use :
select top 100 percent <columns to retrieve)
order by columna, etc.
the view is returning the correct data, but the data isn't being ordered.
If I run the select statement directly, it does work correctly.
Any idea why the views have stopped ordering the data?
Thanks,
Wayne

This is a normal behaviour in SQL2005 !!!! ( and higher ... )

(because order by in the view is only being used to retrieve the date, not to physicaly order the result)

As always in a query, if you want the result in a specific order, you need to add an order by clause to your final select statement !

If you are on sp2 , you can enable a trace flag , but I don't advise using it, because your apps need to be modified ASAP !

trace flag 168HOTFIX: When you query through a view that uses the ORDER BY clause in SQL Server 2005, the result is still returned in random order.

See 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

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

In sql 2005 and higher, Top 100% ... order by is only honoured if it is in the outermost select statement.

This will return in random order

SELECT * FROM (

SELECT top 100 Percent name from sys.objects order by name ) a

while this will return ordered by name

SELECT top 100 Percent name from sys.objects order by name

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