'exists' dilemma

defiant · June 25th, 2003, 12:31 AM

hello people,

someone told me that this statement:

if exists (select top 1 1 from customers) ...

is equivalent in performance to the next one

if exists (select * from customers)

this means that the rows from customers are not actually retrieved when using this last statement (or this is what i've heard).

i couldn't find anything on bol to convince me to use * instead of top 1 1, so i'm asking for your opinion here

thank you,

defiant

David Cameron · June 25th, 2003, 12:39 AM

If I run:

Code:

IF EXISTS (SELECT TOP 1 NULL
           FROM SomeTable)
PRINT 1

IF EXISTS (SELECT NULL
           FROM SomeTable)
PRINT 2

IF EXISTS (SELECT *
           FROM SomeTable)
PRINT 3

in SQL Server 7 with show execution plan I get that the top query takes only 6.95% of the time and the other 2 each take half of the remaining time.

regards
David Cameron

defiant · June 25th, 2003, 01:14 AM

i did the same thing in sql server 2000 and for all 3 queries i see:

Query cost (relative to the batch): 33.33%

defiant.

David Cameron · June 25th, 2003, 01:17 AM

I think there is your answer. In SQL Server the optimiser has converted all three to the same execution plan. In SQL Server 7 the optimiser is not as good and hasn't.

regards
David Cameron

defiant · June 27th, 2003, 07:26 AM

yes, i guess that's it.

thanks,

defiant.