view are not sorting in SqlServer 2005??

For some reason my views are no longer sorting in SqlServer 2005.

I migrated my database from SqlServer 2000 to SqlServer 2005 and now my sort by clause in all my views no longer work. Why is this, and is there a change that I can make to restore sorting in views.


For example, this view does not return the results sorted by name, when before in SqlServer 2000, it did.

create view myView
select top 100 percent name, id  from mytable
order by name



LVL 4
brokeMyLegBikingAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
QPRCommented:
interesting answer!
0
 
TRACEYMARYCommented:
golly that got all the entire page ....strange.....very interesting how that happened.
0
 
brokeMyLegBikingAuthor Commented:
what where you trying to say?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
appariCommented:
strange problem, never observed.
instead of top 100 percent if we use top 100 its working. but we need all the records so i tried the following it works.

try as follows

create view myView
as
select * from (select top 100 percent name, id  from mytable
order by name) as A

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
Anthony PerkinsCommented:
This kludge (adding 100 PERCENT to a VIEW in order to be able to use ORDER BY) still works for me with SQL Server 2005 (Compatibility Level = 90).  However, I would personally never use it as you are essentially limiting the usefulness of the VIEW.  In other words, there should not ne any need whatsoever to use an ORDER BY clause on a VIEW.  The correct way to use a VIEW is as follows:

create view myView

As

select name, id  from mytable


And then call it as follows:
Select * from myView ORDER BY name

Or:
Select * from myView ORDER BY id


0
 
imran_fastCommented:
try using asc
========
create view myView as
select top 100 percent name, id  from mytable
order by name asc
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I could reproduce the same behaviour with the RC2, not sure if in the final release it has been solved...
0
 
brokeMyLegBikingAuthor Commented:

imran_fast, that didn't work.
acperkins, it does not work for me. How did you get it to work, and yes, I do need an ORDER BY in a view. (I realize the performance implications and they are acceptable, given my application architecture)


appari, your suggestion works. Using an absolute number instead of a percent works. So if I just put a really really big number, then my view always sorts successfully:

alter view testJoe as select top 1000000000  * from table1 order by expense
0
 
Anthony PerkinsCommented:
>> I do need an ORDER BY in a view. <<
You are missing the point.

Good luck.
0
 
imran_fastCommented:
wht if you remove percent is it working

create view myView as
select top 100  name, id  from mytable
order by name asc
0
 
brokeMyLegBikingAuthor Commented:
>> no I'm not, you are assuming the usually best way as recommended by the textbooks holds in EVERY case<<

I have an application which dynamically builds sql statements. I want to put as much of the tsql in my databse as possible, rather than in each of the various client applications I have accessing the view. To put the order by clause in the client application is not nearly as elegant as putting it in the view.

imran_fast, your query only returns the top 100 results, if the query has more results than 100, the remaining will not be returned.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.