TOP 100 PERCENT + ORDER BY

I know this is old stuff, but still it is worth to mention.
This issue may cause problems(if you rely on the order results)
when you upgrade SQL Server 2000 to 2005 or 2008.

You cannot create a view/subquery with "order by"
clause without specifying TOP or FOR XML clause.
If you think for a moment there is really no sence to
use "Order By" in a derived table/subquery or View unless
the TOP clause defines what data to return.

The "Order By" clause in the definition of a view, inline function,
derived table or subquery is used only to determine the rows
returned by the TOP clause.

For instance, to determine the TOP 5 records that occur
in a certain order.The "Order By" clause DOES NOT
control the order of those 5 records when they are
returned outside the view.So, to control the order of the
records that are returned by the view, you need to use
ORDER BY in your SELECT * FROM VIEW statement.

Let`s see an example,I will create a table,populate it
with 3 rows.Then create a view on the table with "order by"
with Descending order.
Finally I will try to select from the View.

CREATE TABLE TestTable
(
RowID INT NOT NULL,
SomeName VARCHAR(20) NOT NULL
)

INSERT INTO TestTable (RowID,SomeName)
SELECT 1,'abc1'
UNION ALL
SELECT 2,'abc2'
UNION ALL
SELECT 3,'abc3'
GO

CREATE VIEW vw_Test
AS
SELECT TOP 100 PERCENT RowID,SomeName
FROM TestTable
ORDER BY RowID DESC
GO

SELECT * FROM vw_Test;

-- The results of the select:

RowID SomeName
----------- --------------------
1 abc1
2 abc2
3 abc3

As you can see the results are NOT ordered in descending
order by the RowID column.

The correct way to do it is to use an ORDER BY on the
outermost query block.

SELECT * FROM vw_Test ORDER BY RowID DESC;

RowID SomeName
----------- --------------------
3 abc3
2 abc2
1 abc1

PS: In SQL Server 2000 the result set of the
SELECT * FROM vw_Test
will be ordered in descending order.

Check this good article about the issue:
http://blogs.msdn.com/queryoptteam/archive/2006/03/24/560396.aspx
http://msdn.microsoft.com/en-us/library/ms188385(SQL.90).aspx

Tweet