Inspired by the upcoming presentation by Jen McCown at SQLSat #52 on brushing up your T-SQL I have embarked on a journey to better my own skills.
So, first off the definition of a view is a “virtual table that is outlined by a query and used as a table“.
It also must follow this criteria:
- ORDER BY cannot be used in a view unless there is a TOP or FOR XML specified in the view
- All columns in result must have names
- All column names must be unique
A sample of creating a view would be like so:
CREATE VIEW dbo.DeptStoreCustomers
AS
SELECT * FROM dbo.Customers
WHERE dbo.Customers.custType = 1
So when this is run:
SELECT * FROM dbo.DeptStoreCustomers;
It would return something like:
** Note using * is highly discouraged in production use for most scenarios. But for the sake of simplicity I’m using it here.
Ok, so all this making sense so far? Easy enough right?
So what about changing our view to include the TOP and ORDER BY clauses like this:
CREATE VIEW dbo.DeptStoreCustomers
AS
SELECT TOP (100) PERCENT custID, custName, custType
FROM dbo.Customers
ORDER BY custName
How do you think this will return rows? Probably not like you expect.
Here’s the output on SQL 2008 R2:
SELECT * FROM dbo.DeptStoreCustomers
So wait! Why aren’t the custNames ordered?
The explanation is that the ORDER BY clause does not guarantee the order of the results unless it’s specified in the outer query of the view.
This means that the ORDER BY within the view is utterly useless in this case as it is only used to determine the rows in the TOP clause not in the view itself.
The way to actually get ordered rows would be to query the actual view with the ORDER BY clause added like so:
SELECT * FROM dbo.DeptStoreCustomers
ORDER BY dbo.DeptStoreCustomers.custName
One important thing to note here. In SQL Server 2000 the original query result would have displayed in the correct order. This is because the query plan incorrectly considers the ORDER BY in the view on the total view and not just the TOP clause.
2005 and greater fixed this problem by requiring an outer sort on the view to get this result.
In summary be sure to update all your code to correctly specify the ORDER BY clause in the SELECT clause of the view and not in the view itself. This might save you some heartache in the form of unexpected results when updating from 2000 to newer versions.
