Getting Started with Adobe After Effects - Part 6: Motion Blur
A collection of quick technology learning tips from what people around you learn every day

SQL Server - Do not use aggregate functions to check existance of the data

Nov 24 2011 12:06AM by Madhivanan   

Aggregate functions always return at least one row. So using aggregate functions to check for the existance of data will not give correct results. See the results returned from these queries

declare @t table(i int)

if exists(select i from @t)
select 'yes'
else
select 'no'

if exists(select max(i) from @t)
select 'yes'
else
select 'no'

While the first query returns no, the second query returns yes.

For more informations refer http://beyondrelational.com/blogs/madhivanan/archive/2011/11/22/aggregate-functions-always-return-at-least-a-row-although-table-is-empty.aspx

Read More..   [0 clicks]

Published under: SQL Server Tips ·  ·  ·  · 


Madhivanan
3 · 40% · 12862
22
 
11
 
15
 
0
Incorrect
 
0
Interesting
 
0
Forgotten



Submit

10  Comments  

  • 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.

    commented on Nov 23 2011 11:51PM
    Jacob Sebastian
    1 · 100% · 32220
  • Good tip .

    commented on Nov 24 2011 10:11PM
    Deepak kelath
    210 · 1% · 222
  • 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.

    commented on Nov 25 2011 8:49AM
    Roby Van Hoye
    222 · 1% · 209
  • 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!

    commented on Nov 26 2011 12:23PM
    Nakul Vachhrajani
    4 · 36% · 11543
  • 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.

    commented on Nov 28 2011 11:30AM
    david.blair@eagletm.com
    1337 · 0% · 17
  • 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).

    commented on Nov 28 2011 11:54AM
    Roby Van Hoye
    222 · 1% · 209
  • Wrong use of exists (Select Count(*) From @TableName)

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

    commented on Nov 28 2011 11:12PM
    harishs
    283 · 0% · 150
  • 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.

    commented on Nov 29 2011 12:20AM
    Adam Tokarski
    58 · 3% · 1021
  • 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 ...

    commented on Apr 4 2012 3:00AM
    Michel Bernard
    652 · 0% · 52

Your Comment


Sign Up or Login to post a comment.

"SQL Server - Do not use aggregate functions to check existance of the data" rated 5 out of 5 by 22 readers
SQL Server - Do not use aggregate functions to check existance of the data , 5.0 out of 5 based on 22 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]