For many years it’s been a best practice to never put an ORDER BY in a view. The idea is that a view shouldn’t have an inherent order. Just like any other query. If you want the data from a view ordered then you query the view with an ORDER BY clause. In fact if you put an ORDER BY in a view you’ll get an error:

Msg 1033, Level 15, State 1, Procedure MyView, Line 4 [Batch Start Line 2]

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

But to get around this, for years people have been putting a TOP 100 PERCENT in the view as well as the ORDER BY. At that point the view works.

-- Set up demo table
CREATE TABLE [dbo].[table1] (id int NOT NULL PRIMARY KEY, col2 varchar(10));
CREATE UNIQUE INDEX [ix_table1] ON [dbo].[table1](col2);
GO
INSERT INTO [dbo].[table1] VALUES (1,'abc'),(2,'def');
INSERT INTO [dbo].[table1] VALUES (3,'zzz'),(4,'aaa');
GO

CREATE VIEW [dbo].[MyView] AS
SELECT TOP 100 percent *
FROM [dbo].[table1]
ORDER BY id;
GO

And the view gets created without an error. What I found interesting is that as far back as I can go (2008 R2) the ORDER BY is completely ignored.

SELECT * FROM [dbo].[MyView];
GO

So there you go. Not only is putting an ORDER BY in a view considered a bad idea it has absolutely no effect.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: microsoft sql server, order by, T-SQL, views