Sorting problem in SQL 2005

Question

Post reply

Sorting problem in SQL 2005

medhat.awad

Grasshopper

Points: 23
More actions
June 8, 2008 at 5:13 am

#208175

I have two SQL Servers 2000 and 2005 SP2.
I'm trying to restore databases from SQL 2000 into SQL 2005.
after restoring all views sorting (Order By) is not working.
Is there any solution?
Thanks
Medhat

Viewing 8 posts - 1 through 8 (of 8 total)

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

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 1

I am going to assume that you have the following in your view:

CREATE VIEW dbo.MyView AS

SELECT TOP 100 PERCENT ... ORDER BY someorder

GO

If so, I don't know of a fix for this. SQL Server 2005 fixed this 'bug' that allowed a view to be ordered. To fix, you need to modify all code that references the views and put the order by on that select. For example:

Current query: SELECT * FROM dbo.MyView;

Fixed query: SELECT * FROM dbo.MyView ORDER BY someorder;

All of the information available on Connect shows that this will not be fixed since it is working as designed.

Jeff

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

medhat.awad Grasshopper Points: 23 More actions · Answer 2

Thanks for your reply.

I found hotfix from microsoft to solve this problem

http://support.microsoft.com/kb/926292/en-us

I applied it but it's not working also.

I think the only solution is to modify the Select statement to replace Top 100 Percent by Top 2147483647

Thanks

Tom Bouman Newbie Points: 3 More actions · Answer 3

Try this one:

Select top 1000 strSomething

From tblMyTable

With (index(idxSomething))

assuming you have created an index idxSomething on strSomething

Goodluck,

Tom

GSquared SSC Guru Points: 260824 More actions · Answer 4

Order By works in views in SQL 2005. Are you using column aliases in the Select clause? Do you have two columns in the view with the same name? Please include a copy of the view in the post.

- 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

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 5

Order By in SQL Server 2005 does work, however - it only works to guarantee the order to satisfy the TOP requirement and does not guarantee the order the results will be returned to the client.

The only way to guarantee the order of the results returned to the client is to include an order by statement at the client.

Jeff

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

GSquared SSC Guru Points: 260824 More actions · Answer 6

Not precisely. Are you querying the view, or connecting the client directly to the view?

If querying the view, yes, the Order By (or lack thereof) of the calling query takes precedence over the Order By in the view. If connecting directly to a view from a client, my experience is that it still uses the Order By of the view, and the actual statement in Books Online doesn't contradict that.

- 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

medhat.awad Grasshopper Points: 23 More actions · Answer 7

Yes I'm querying the view directly from the code, I'm doing the order by in the view instead of the code and it was working with SQL 2000.

I have modified the views to replace the TOP 100 PERCENT with TOP 2147483647 and it's working fine to query the view directly from the code.

Thanks for helping

Medhat