?
Solved

SQL Server 2005 SP3 Order by

Posted on 2010-01-12
9
Medium Priority
?
371 Views
Last Modified: 2012-05-08
Hey Guys,

Using server 2003 and sql 2005 sp3.
MS Link I found:http://support.microsoft.com/kb/926292

"      The SELECT statement uses the TOP (100) PERCENT expression.
"      The SELECT statement uses the ORDER BY clause.

When you query through the view, the result is returned in random order.

But for some reason the patch is not fixing the problem.
Please guide.

0
Comment
Question by:Shivtek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26297783
for on the safer side, you need to put the ORDER BY when calling the View

SELECT * from yourView order by Column1
0
 
LVL 1

Author Comment

by:Shivtek
ID: 26297958
I am not the programmer, just getting a possible solution for a programmer. Can you please elaborate a bit more.

Thanks
0
 
LVL 41

Accepted Solution

by:
ralmada earned 1336 total points
ID: 26299350
Use this workaround:
select top 99 percent * from yourview order by column1
Check this link for details:
http://sqlservernation.com/blogs/brandongalderisi/archive/2009/03/15/when-ordering-in-a-view-doesn-t-work.aspx 
0
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 1336 total points
ID: 26299436

sorry I mean
create view yourview as
select top 99 percent * from yourtable order by column
 
Check the link I've mentioned for details.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 664 total points
ID: 26306524
You should never have an ORDER BY clause in a VIEW as it is a very bad idea.

But to answer your specific question, what you have discovered is the fact that MS finally (with MS SQL Server 2005) fixed the problem where developers were using the kluge SELECT 100 PERCENT ... ORDER BY.

You can continue looking for cheesy workarounds such as using SELECT TOP 99.99999 PERCENT .... ORDER BY or make sure the problem is fixed correctly as Aneesh has suggested.  In other words, remove the TOP 100 PERCENT and ORDER BY clause and apply the ORDER BY to the VIEW.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 26306644
I would tend to agree with acperkins, Honestly I've never used order by inside a view because it's a bad practice. but I was just showing the workaround. Whether you want to call it cheesy or not, it's up to you. LOL
And actually because of the asker posting I've become aware that SP3 fixed that problem. :)
0
 
LVL 1

Author Comment

by:Shivtek
ID: 26346633
This used to work fine when I was using SQL 2000.

Not sure why wont work with SQL 2005.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26353243
>>Not sure why wont work with SQL 2005.<<
Trust me there were many kludges like this fixed in SQL Server 2005.  So perhaps it is time that you start doing it right, rather than wait for the next version to break your code all over again.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 26353258
>>This used to work fine when I was using SQL 2000.<<
Alternatively, you can stick with SQL Server 2000, just understand that support for this version has now expired.
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question