[thelist] Quick SQL question

[Joshua Olson]

----- Original Message -----
From: ".jeff" <jeff at members.evolt.org>
Sent: Monday, February 10, 2003 5:42 PM


> actually, i just ran the results on a table with 8400 records and am
seeing
> some difference in times.  your version executes consistently about 2/3 of
> the time it takes mine to execute.

Jeff, et al,

In case you are wondering why Jason's query ran faster I may offer a
layman's explanation.  Jeff, your subquery returns 15 values from the table
of 8400 records and then executes a second query against the same table of
8400 records.  Jason, your query pulls 15 records from the table and then
simply returns the 15 records in reverse order.  If Col_ID is an index on
the table, then the reversal is a trivial transformation.

Usually, though not always, the syntax offered by Jason will be faster.

FWIW, the syntax offered by Jason is basically creating a view and then
executing a query against that view.  The view being:

SELECT TOP 15 * FROM MyTable ORDER BY Col_ID DESC

To illustrate that the inner query is semantically equal to a view in SQL
Server, try removing the "TOP 15" from the query:

SELECT * FROM
(SELECT * FROM MyTable ORDER BY Col_ID DESC) MyTable
ORDER BY Col_ID ASC

Obviously this is an asinine query, but it illustrates the fact that the
inner SELECT is being treated as a view.  As you would expect (as with a
view) the parser returns an error on the ORDER BY clause.  A common trick
around this with SQL Server is to use TOP 100 PERCENT.

-joshua