Yeah, I have seen people making this mistake. The pattern I have seen commonly is IF EXISTS( SELECT COUNT(*) FROM table) where the developer forgets that if there are no rows, the query will still return 1 row with a 0 in it.

Good tip .

To add insult to injury, when you use an aggregate function to check if there is something in a table, the query will cause the entire table to be scanned which can be painfully slow in case there is a lot of data in the table. When simply using IF EXISTS (SELECT * FROM ..) the server will return as soon as it finds the first row causing a lot less I/O & CPU

I haven't seen the above tests in the wild yet, but the IF (SELECT COUNT(*) FROM table) = 0 seems to be quite popular and suffers from the above remark.

I wonder if there are any developers or administrators out there who use anything other than the (SELECT * FROM...) or (SELECT COUNT(*) FROM...) methods. Even with (SELECT COUNT(*) FROM...), I agree that I have frequently seen cases where the developers have forgotten to check for the evaluation to be > 0.

Personally, I use IF EXISTS (SELECT TableName.PrimaryKeyColumn FROM TableName) to check for existance of data in a table.

As I said, it would be interesting to see who uses other aggregation functions to check for data existance - and why?

@Madhivanan: Thank-you for sharing!

I use IF EXISTS (SELECT null FROM .. ) as a programming standard. Once you get used to it, it makes statements with existence tests easier to read. I got this tip from the O'Reilly book SQL Cookbook.

I doubt there is much difference between

  - IF EXISTS ( SELECT NULL FROM myTable ...)
  - IF EXISTS ( SELECT * FROM myTable ...)
  - IF EXISTS ( SELECT 1 FROM myTable ...)
  - IF EXISTS ( SELECT field FROM myTable ...)

There used to be this 'myth' that selecting 1 was 'lighter' as the server would only need to read a single int instead of the then considered 'bad' * that would cause all fields to be brought into memory before the EXISTS() could look at it. I can't remember up to which version this myth had some truth in it, but in each case I believe that since SQL2000 the Query Optimizer recognizes this and using the Qury Plan it's quite easy to show that the following statements are all equivalent AND they only fetch 11 bytes from the table each.

SELECT Hello = 'World' WHERE EXISTS ( SELECT * FROM AdventureWorks.Production.Location WHERE CostRate = 25.00)
SELECT Hello = 'World' WHERE EXISTS ( SELECT 1 FROM AdventureWorks.Production.Location WHERE CostRate = 25.00)
SELECT Hello = 'World' WHERE EXISTS ( SELECT NULL FROM AdventureWorks.Production.Location WHERE CostRate = 25.00)
SELECT Hello = 'World' WHERE EXISTS ( SELECT LocationID, Name, CostRate, Availability, ModifiedDate FROM AdventureWorks.Production.Location WHERE CostRate = 25.00)

Personally I prefer the first version as the second one reminds me too much of the non-optimization, the last one is too much work too type and the SELECT NULL somehow feels ...well .. wrong... checking if something exists by means of the equivalent of nothing/unknown/undetermined... meh =) I guess the tip was based on the same myth (as NULL defaults to an int).

Wrong use of exists (Select Count(*) From @TableName)

Correct Use of exists: If Exists(Select * From @TableName)

I'm startled with possibility of EXISTS(SELECT NULL... Shouldn't MSServer recognize it as NULL and therefore send false to EXISTS? Maybe its bug? Logical NULL is neither TRUE nor FALSE, but in that case it's rather in meaning FALSE.

As the discussion has moved from the use of aggregate functions to the performance aspect, you may be interested in my post where I compare the various known methods of counting the number of rows in a table with respect to performance and accuracy - http://beyondrelational.com/blogs/nakul/archive/2011/04/04/measuring-the-number-of-rows-in-a-table-are-there-any-alternatives-to-count.aspx

Another myth is to believe that in subqueries and if statements, exists and in perform faster than not exists and not in when the values in the where clause are not indexed. It was justified by the statement that "For exists and in, SQL Server can return TRUE as soon as a single row matches. For the negated expressions, it must examine all values to determine that there are not matches." (extracted from Sybase SQL Server 11 - Performance and Tuning Guide).

This is obviously false, as for negated expressions, SQL Server will return false as soon as a single row matches, and for exists and in, it must also examine all values to determine that there are not matches. I did extensive tests with timings and query plans to prove it.

They recommended writing:

if exists (select * from table where...)
    begin
        goto exists_label
    end
/* Statement group */
exists_label:

instead of:

if not exists (select * from table where...)
    begin
        /* Statement Group */
    end

This optimization tip is not present anymore in the latest versions of the Sybase ASE Performance and Tuning Guide ...