Interesting observation about VIEW and ORDER BY clause in SQL Server 2008

Monday, November 23, 2009 |

View is been always interesting topic for me and for many of the SQL professionals. Use of ORDER BY clause inside the view is really a question of debate since long. I am going to present my observation about view in this article which I have tested in SQL Server 2008 (10.0.1600.22 ((SQL_PreRelease).080709-1414 ))

It is not guaranteed that it will remain same in all versions of SQL Server.

Basically, you can’t create VIEW with ORDER BY clause. However, if you will use TOP clause inside your SELECT clause, you can specify ORDER BY clause.

NOTE: why Order by is not working with VIEW and why it is working with TOP clause is also an interesting topic which I will discuss sometime later.


Anyway, let us go through some small practical code snippet which is really basic but useful to make fundamental clear.

CREATE TABLE ViewTest
(
      col1 CHAR(1)
)
GO

INSERT INTO ViewTest
SELECT 'A' UNION ALL
SELECT 'C' UNION ALL
SELECT 'D' UNION ALL
SELECT 'B' UNION ALL
SELECT 'Z' UNION ALL
SELECT 'R'
GO

select Col1 from ViewTest order by col1
GO
--Result of above query
--Col1
------
--A
--B
--C
--D
--R
--Z

--(6 row(s) affected)

CREATE VIEW vViewTest AS
select Col1 from ViewTest order by col1
GO
--creation of this view won't work rather,
--you will be greeted with following error
--Msg 1033, Level 15, State 1, Procedure vViewTest, Line 2
--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.

--you can't use ORDER BY clause in View that's why you faced an error.
--even if you will try use TOP clause, you can specify ORDER BY

CREATE VIEW vViewTest AS
select TOP 100 PERCENT Col1 from ViewTest order by col1
GO

SELECT * FROM vViewTest
GO

--result of above query
--Col1
------
--A
--C
--D
--B
--Z
--R

--(6 row(s) affected)

Result is not sorted, however it was working in SQL Server 2000 but right now it is not working in SQL Server 2005+ versions.

Let us try one more thing here.

ALTER VIEW vViewTest AS
select TOP 99.999 PERCENT Col1 from ViewTest order by col1
GO

SELECT * FROM vViewTest
GO

--result of above query is.
--Col1
------
--A
--B
--C
--D
--R
--Z

--(6 row(s) affected)

Bingo!!! it is working now but this is I guess error or something so I recommend not to trust this 99.999 stuff and simply use following solution for ordering your view. If you right now use “99.999” stuff and in next patches or in next fixes, it might stop working but if you will ordering your result set while executing select, it is guaranteed that ordering will work 100% sure. For eg:

ALTER VIEW vViewTest AS
select Col1 from ViewTest
GO

SELECT * FROM vViewTest order by col1
GO

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

0 comments: