You all are familiar with the views in SQL Server. They serve a lot of purposes, but they have there limitations too. One of the limitations of the view is that you can not have an ORDER BY statement a part of the query in SQL Server. I was never able to understand the logical reason for this constraint.
One of my students Paulo Underland V Rocha (phew..... long name) from Norway. He wanted to avoid this problem and we were looking at all the different methods to implement ordering the data inside the view (or in any object which can return a table).
But, if you read the error message of SQL Server when you try to do something like this
CREATE VIEW vContactListByEmail
AS
SELECT EMailAddress, FirstName, LastName
FROM Person.Contact
ORDER BY FirstName
Go
is
Msg 1033, Level 15, State 1, Procedure abc, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
So, by principal you can not use it in views functions, derived tables.....
but then we tried using this
CREATE VIEW vContactListByEmail
AS
SELECT TOP 100 PERCENT EMailAddress, FirstName, LastName
FROM Person.Contact
ORDER BY FirstName
Go
since, if you know, you can use ORDER BY if you use the top clause. This worked, but to our surprise, it didnt showed us the data in the sorted order. But it was showing us the sorted data if we used any other value except 100. So finally, we used this query
CREATE VIEW vContactListByEmail
AS
SELECT TOP 99.999999999 EMailAddress, FirstName, LastName
FROM Person.Contact
ORDER BY FirstName
Go
and this one finally worked and gave us all the rows from the table in the sorted order from the table.
Simple isn't it ?
