Question
· Oct 7, 2019

&SQL and ORDER BY ID DESC

I know &SQL returns only one result but is 

&SQL(SELECT ID FROM Cinema.Film ORDER BY ID DESC)

and

&SQL(SELECT TOP 1 ID FROM Cinema.Film ORDER BY ID DESC)

the same in terms of processing required?

Discussion (7)3
Log in or sign up to continue

&SQL(SELECT ID FROM Cinema.Film ORDER BY ID DESC)

similar to the query

&SQL(SELECT TOP ALL ID FROM Cinema.Film ORDER BY ID DESC)


Therefore, it is natural that the query plans

&SQL(SELECT TOP ALL ID FROM Cinema.Film ORDER BY ID DESC)
and
&SQL(SELECT TOP 1 ID FROM Cinema.Film ORDER BY ID DESC)

are so significantly different.

While using &SQL without a CURSOR will always only execute once and return one row, the SQL optimization is still dependent on the specific query - so while the cost of the non-top query is much higher, what really matters here is cost to first row.  For the example query, that will be minimal, but could be much higher in a more complicated case.