Views and Order By

Question

Views and Order By

mohnish_khiani

Newbie

Points: 7
More actions
November 2, 2011 at 5:06 am

#247042

Why the order by clause cannot be used in the query which makes up the query in a view??

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Because there should be no expectation that the results of a SELECT query will be ordered, whether the query is against a table or a view, and whether or not that table or view has a clustered index. The only way to guarantee ordered data is to put an ORDER BY in the query. Therefore it makes no sense to allow ORDER BY in the view definition. The only exception to this is if your ORDER BY clause contains a TOP operator.

John

JonFox SSCrazy Points: 2157 More actions · Answer 2

Views, like tables, are considered to be unordered sets. Take a look at this MSDN article: http://msdn.microsoft.com/en-us/library/ms188723.aspx From the article:

Although the view definition contains an ORDER BY clause, that ORDER BY clause is used only to determine the rows returned by the TOP clause. When querying the view itself, SQL Server does not guarantee the results will be ordered, unless you specify so explicitly, as shown in the following query:
SELECT * FROM TopView
ORDER BY LastName;

JonFox SSCrazy Points: 2157 More actions · Answer 3

..and John Mitchell wins today's quick-draw contest... 😀

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

mohnish_khiani (11/2/2011)
Why the order by clause cannot be used in the query which makes up the query in a view??

Not only in SELECT statement, views be used in other DML statements (INSERT, DELETE, and UPDATE) as well.

ORDER BY clause is not allowed in INSERT, DELETE and UPDATE.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Suresh B. (11/2/2011)
mohnish_khiani (11/2/2011)
Why the order by clause cannot be used in the query which makes up the query in a view??
Not only in SELECT statement, views be used in other DML statements (INSERT, DELETE, and UPDATE) as well.
ORDER BY clause is not allowed in INSERT, DELETE and UPDATE.

I think you need to be a little more specific, Suresh:

DROP TABLE #Temp

SELECT TOP 1 [Name]

INTO #Temp

FROM sys.COLUMNS

ORDER BY [Name]

INSERT INTO #Temp ([Name])

SELECT [Name]

FROM sys.COLUMNS

ORDER BY [Name]

-- (1 row(s) affected)

-- (6676 row(s) affected)

;WITH CTE AS (select top 100 * FROM #Temp ORDER BY [Name] DESC)

DELETE FROM CTE

-- (100 row(s) affected)

;WITH CTE AS (select top 100 [Name] FROM #Temp ORDER BY [Name] DESC)

UPDATE CTE SET [Name] = 'Overwritten'

-- (100 row(s) affected)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden