Solved

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

Posted on 2011-03-07
6
442 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 13

Assisted Solution

by:LIONKING
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.
0
 
LVL 25

Accepted Solution

by:
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).

0
 
LVL 29

Expert Comment

by:sammySeltzer
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
0
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
LVL 29

Expert Comment

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

Expert Comment

by:8080_Diver
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. ;-)
0
 
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.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
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 video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

729 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