Select * or Select 0 when using Exists

Question

Post reply

Select * or Select 0 when using Exists

Jeet

SSCommitted

Points: 1536
More actions
May 27, 2004 at 9:51 am

#108438

Has anyone any views on whether doing a
IF Exists(select 0 from atable where...)
is more or less efficient than
IF Exists(select * from atable where...)

Thanks Jeet

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

sqlpro Hall of Fame Points: 3859 More actions · Answer 1

ur first query always faster than 2nd one.

in the second one u r getting all data(select *) to memory where as in the first one there was none

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 2

I usually use IF EXISTS (SELECT TOP 1 NULL FROM atable WHERE ...)

Thus, I ensure that the minimum work is done on the server, since it is only necessary to check the existence of the first row that satisfies the search condition. Of course, if you search by a unique index, this does not make any difference.

Regards,

Goce Smilevski.

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 3

Actually the EXISTS( subquery ) just returns Boolean if subquery contains any rows.

Performancewise there is no difference in the different approaches mentioned here. Sql Server does not have to bring all the rows / data into memory, just check the existance of any row in the subquery.

All queries generate the same Execution Plan and take the same time to execute.

/rockmoose

You must unlearn what You have learnt

appolden Mr or Mrs. 500 Points: 567 More actions · Answer 4

It makes no difference which method you use. A value if true is returned as soon as a row matching the criteria has been found.

If you still not convinced show the execution plan and run the script below in query analyser. They all generate the same execution plan.

use northwind

if exists (select * from dbo.Customers)

BEGIN

PRint '123'

END

IF exists (select top 1 CustomerID from dbo.Customers)

BEGIN

PRint '123'

END

IF exists (select CustomerID from dbo.Customers)

BEGIN

PRint '123'

END

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 5

Ok, it doesn't make a difference which way to use, I didn't say it will. I only think that the way I prefer, seems to me like the clearest way to tell SQL Server what to do. Dispite the fact that SQL is clasified as a declarative language, most of the time it appears not to be 100% correct. The performance of a SQL statement depends a lot on the way it is written....so much of the declarativeness.

Anyway, tha answer to this question is probably a matter of religion.

Jeet SSCommitted Points: 1536 More actions · Answer 6

Guys,

Thanks for the help. Much Appreciated.

Thanks Jeet

smv929 SSCrazy Points: 2889 More actions · Answer 7

Similarly, I know that using count(fieldname) vs count(*) can cause a problem. By using count(*), SQLSvr's optmizer determines the best field to use based on primary keys/indexes. However, you may have a performance problem when you specify count(fieldname) and fieldname is not indexed or does not provide the index necessary for good performance. Of coure, you will often know which field is a primary key or index. (I read this in Sajat Dam's book SQL Server Query Performance Tuning Distilled which is a great book.)

smv929

Sunny-138471 Ten Centuries Points: 1162 More actions · Answer 8

But Count(fieldName) is required in case you want to eliminate Nulls from the count. Am I right?

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 9

Well, This is straight from BOL - EXISTS

A. Use NULL in subquery to still return a result set

This example returns a result set with NULL specified in the subquery and still evaluates to TRUE by using EXISTS.

USE NorthwindGOSELECT CategoryNameFROM CategoriesWHERE EXISTS (SELECT NULL)ORDER BY CategoryName ASCGO

But yes Sunny, if you are performing a count:
count(*) will count nulls
count(fieldname) wuill not count nulls

/rockmoose

You must unlearn what You have learnt