Solved

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

Posted on 2011-03-07
6
434 Views
Last Modified: 2012-05-11
Hi,

Can you please explain me:

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

Thanks
0
Comment
Question by:milani_lucie
6 Comments
 
LVL 13

Assisted Solution

by:LIONKING
LIONKING earned 250 total points
Comment Utility
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
 
LVL 25

Accepted Solution

by:
reb73 earned 250 total points
Comment Utility
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
 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility
View doesn't like explicit ORDEr BY clause when creating it...sorry
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now