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

Posted on 2011-03-07
Last Modified: 2012-05-11

Can you please explain me:

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

Question by:milani_lucie
LVL 13

Assisted Solution

LIONKING earned 250 total points
ID: 35059382
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.
LVL 25

Accepted Solution

reb73 earned 250 total points
ID: 35059387
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).

LVL 28

Expert Comment

ID: 35059401
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

LVL 28

Expert Comment

ID: 35059408
View doesn't like explicit ORDEr BY clause when creating it...sorry
LVL 22

Expert Comment

ID: 35060629
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. ;-)
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35065743
>>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.

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
kill process lock Sql server 9 63
SQL Error - Query 6 40
Negative isnull? 3 14
Parse this column 6 23
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

791 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