Microsoft, SQL Server, 2005, view with order by clause

Hi,
I have crated a view with Top 100 Percent in the Select Clause and an order by Column name. It is working fine when I execute the view in the Enterprise Manager.

But When I put,

Select * from View1

It is not giving the sorted result.
LVL 4
batchakamalAsked:
Who is Participating?
 
imitchieConnect With a Mentor Commented:
well in that case,
select top 100000000 *
from table
order by col1
0
 
imitchieCommented:
this works right?
Select * from View1 order by columnname
i think top 100% order by x simply tells it to collect: up to 100%, by order of column x.  it can do that using multiple threads and arrive at some result. but because it's a view, it's the the final result. the result gets dumped in any order (that threads complete)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
in views, you need to use the following method to allow sortings:


CREATE VIEW view_name 
AS 
SELECT TOP 100 PERCENT ... 
FROM ... 
WHERE ... 
ORDER BY ...

Open in new window

0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, I notice you did that :(

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
how many CPU does your server have?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Check whether there exists another view with the same name and different owner  ?
0
 
batchakamalAuthor Commented:
We have Only one processor
0
 
batchakamalAuthor Commented:
If I put

Select * From View1 Order By Column2

It is working fine. But I require it to sort when I select.
0
 
batchakamalAuthor Commented:
There is only one view with that name.
0
 
batchakamalAuthor Commented:
It Works out. But I would like to know whether this will affect our performance or not.
0
All Courses

From novice to tech pro — start learning today.