Ordering the result of Views – SS SLUG Dec 2013 – Brain Bashers - Demo VIII

First of all, Happy New Year 2014! May your all dreams come true in 2014!

This post is related to the presentation “SQL Brain Bashers” I did at SQL Sever Sri Lanka User Group Meeting. This topic is very old but still it needs to be discussed for beginners; it is all about having ORDER BY with VIEWs.

Here is the question related to this demo;

Does SQL Server sort the resultset returning from a view based on ORDER BY clause added?

Answer is “NO”. Have a look on below code and then see the explanation.

USE AdventureWorks2012

GO

-- this throws an error

-- read the error properly

CREATE VIEW v_SalesOrdersDetails

AS

SELECT SalesOrderID, SalesOrderDetailID

    , CarrierTrackingNumber, OrderQty

    , ProductID, LineTotal

FROM Sales.SalesOrderDetail

ORDER BY ProductID DESC

GO

-- this creates the view

CREATE VIEW v_SalesOrdersDetails

AS

SELECT TOP 100 PERCENT 

    SalesOrderID, SalesOrderDetailID

    , CarrierTrackingNumber, OrderQty

    , ProductID, LineTotal

FROM Sales.SalesOrderDetail

ORDER BY ProductID DESC

GO

-- this query does not sort the

-- result by product id

SELECT * FROM v_SalesOrdersDetails 

-- this sorts as we have added

-- to the query

SELECT * FROM v_SalesOrdersDetails 

ORDER BY ProductID DESC 

Here is the result;

As you see, the ORDER BY clause we have added to the view has no effects.

Let’s try to understand this behavior. Remember the error thrown from first try. It clearly says that ORDER BY cannot be used with VIEWs. Looks weird :)? There is nothing wrong with it. View is supposed to represent a table. A table is a logical entity that does not hold ordered rows, hence view cannot hold ordered rows too. However SQL Server allows to use ORDER BY clause with views when TOP or FOR XML is used. Again, remember, though you have used ORDER BY with TOP, it does not sort the result, it uses ORDER BY for completing TOP operation only (Note: Generally it uses ORDER BY for two purposes; Which rows to be picked for the TOP, and order the result).

There is a misconception that adding TOP 99.99 PERCENT sorts the result set based on ORDER BY added. The fact is, it is wrong too, SQL Server does not guarantee a sorted resultset. Not only that, the result set might not contain all records returned.