Author: Gilles Duchêne
The
result of a view cannot be sorted. For a lot of developers, this feels like a
shortcoming in the SQL language.
Instead
of applying the correct syntax:
Creating
& using the view:
CREATE VIEW view
AS SELECT fields FROM table
SELECT fields FROM view ORDER BY fields
A
lot of people like to write:
CREATE VIEW view AS SELECT TOP 100
PERCENT fields FROM table ORDER BY fields
SELECT fields FROM view
This
looks like a fine workaround, because the TOP PERCENT clause allows you to use
the ORDER BY clause in a view (or subquery). However, views or subqueries
should only select the rows you need and should never sort them. Sorting of
rows must be done as a last step of a query.
So,
why can you use ORDER BY in a subquery or view when you use the TOP PERCENT
clause too? Because TOP PERCENT + ORDER BY is not intended to sort rows, but to
define which rows you want to select, in other words, it is a criterium and not
a sort clause. If you want only the first half of the rows of a table (this is
a criterium), you can specify TOP 50 PERCENT, but you need of course to specify
the field where your TOP 50 PERCENT is based on (is it date, amount, id, name,
…, this is the ORDER BY). And this is the big misunderstanding: TOP
PERCENT with ORDER BY is a criterium and not a SORT clause.
OK,
so? What's the problem, it works, and TOP 100 PERCENT is a good workaround.
Wrong,
it worked with SQL Server 2000, but not with SQL Server 2005.
Why?
Because in a subquery "SELECT TOP 100 PERCENT", the query optimizer
'recognizes' that this means 'the whole table', so it will not execute the TOP
100 PERCENT with the ORDER BY clause, but it will instead do a table scan
(remember that this subquery is only for limiting the number of rows! So, TOP
100 PERCENT = whole table).
Try
this example on SQL Server 2000 and 2005 and you will notice the difference:
CREATE
TABLE dbo.tblNR
(
nr
int
)
Add
some records:
INSERT
INTO dbo.tblNR (nr) VALUES (1)
INSERT
INTO dbo.tblNR (nr) VALUES (2)
INSERT
INTO dbo.tblNR (nr) VALUES (3)
Create
a view:
CREATE
VIEW dbo.vwNR AS
SELECT
TOP 100 PERCENT nr FROM dbo.tblNR ORDER BY nr DESC
Use
the view:
SELECT
* FROM dbo.vwNR
With
SQL Server 2000, the output is
3
2
1
With
SQL Server 2005 the output is
1
2
3
If
you compare both execution plans:
In
SQL Server 2000
In
SQL Server 2005
That
SQL Server 2005 is more efficient becomes clear if you execute following
statement:
SELECT
* FROM dbo.vwNR ORDER BY nr
In
this case SQL Server 2000 will execute a double sort operation (first
descending, than ascending):
Conclusion:
do not abuse the TOP PERCENT clause to sort rows.