Why can not we have ORDER BY clause in CREATE VIEW (SELECT) statement - SQL Server ?

Hi,

Can you please explain me:

Why can not we have ORDER BY clause in CREATE VIEW (SELECT) statement ?

Thanks
milani_lucieAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
reb73Connect With a Mentor Commented:
Because a view is just a definition and does not actually contain data as such..

Ordering is done when you query the results or store it (clustered indexes).

0
 
LIONKINGConnect With a Mentor Commented:
You can use the ORDER BY clause when you query the view, you don't need the ORDER BY when you create it, because at that momento you're not retrieving the data from the server, just "creating" the view.
0
 
sammySeltzerCommented:
Try something like this:

Create View ViewName as SELECT *  FROM (SELECT fieldname1, fieldname2...
from yourTable ORDER BY fieldname) as t

View doesn't like explicit ORDEr BY clause
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
sammySeltzerCommented:
View doesn't like explicit ORDEr BY clause when creating it...sorry
0
 
8080_DiverCommented:
There are decisions you make and those you live with.  In this case, Microsoft gets to make the decision and you get to live with it. ;-)
0
 
Anthony PerkinsCommented:
>>In this case, Microsoft gets to make the decision and you get to live with it. ;-) <<
I have to remember that one.  That is great!

But we can only hope the author does not resort to using TOP 100 PERCENT or some other cheesy kludge or they will be very disappointed when they discover that the ORDER BY clause is ignored.

But the simple answer is that even if they could use ORDER BY, it is a very bad idea.
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.