SQL SERVER- IF EXISTS(Select null from table) vs IF EXISTS(Select 1 from table)

Few days ago I wrote article about SQL SERVER – Stored Procedure Optimization Tips – Best Practices. I received lots of comments on particular blog article. In fact, almost all the comments are very interesting. If you have not read all the comments, I strongly suggest to read them. Click here to read the comments.

The most interesting comment conversation is among Divya, Brian and Marko. Please read the comments of Marko for sure. It is the comment, which has triggered this post.

Comments by Divya

I have seen in one of the blogs to use EXISTS like

IF EXISTS(Select null from table)

Will it optimize the perfomance better than

IF EXISTS(Select 1 from table)?

Comments by Brian Tkatch

@Divya

It makes no difference what is put there.

Brad Schulz has an interest article on it: http://bradsruminations.blogspot.com/2009/09/age-old-select-vs-select-1-debate.html

Even 1/0 is allowed! Obviously, it is not evaluated.

Technically, however, * does get expanded, adding some minuscule amount of time: http://www.sqlskills.com/BLOGS/CONOR/2008/02/default.aspx?page=2

So, anything other than * takes the same amount of time. * takes an iota longer. Personally, i use *, to show that i do not care what the results are.

Comments by Pinal Dave

Hello Divya,

I not think so because there is no significant difference in transferring of 1 byte and a null value.

Other than that there is no difference between these two queries.

Regards,

Pinal Dave

Comments by Marko Parkkola

“I not think so because there is no significant difference in transferring of 1 byte and a null value.”

While that is true in this exact case I like to be pedantic about this and elaborate this a bit and probably confuse everybody up :)

There is no concept NULL value if you think about C code or even the CPU. Pointers in C can have NULL values but that just means that the pointer is pointing to memory in the address 0×00000000 (in 32-bit machine). The actual pointer is still taking sizeof(VOID*) amount of memory even when it points to NULL.

Now if you think about functions in C or any other language they always reserve space in the function stack for the return value. You can say, again in C, that you don’t care about the return value and declare the function to return “void” but still it takes 4 bytes of memory in 32-bit machine for return value.

And actually, of you think about the CPU, there is 32 bit register reserved exactly for this.

And now considering that it makes no difference to return one byte or four bytes since there is space for four bytes anyway. And in fact, many times (all the times?) when you deal with C data types CHAR (1 byte) or SHORT (2 bytes) you end up taking 4 bytes because of the padding to keep memory aligned.

Uh! I almost felt like going back in time some ten years when I was writing kernel drivers for Windows :)

This is excellent participation from experts like Marko and Brian. I really like the last line of Marko where it mentioned kernel drivers of Windows. Bravo everybody!

If you have any suggestion please leave a comment in original article.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

42 thoughts on “SQL SERVER- IF EXISTS(Select null from table) vs IF EXISTS(Select 1 from table)

  1. Hi Pinal. I really thank you for the kind words.

    I must just elaborate that I didn’t write kernel drivers of Windows, like that I would have worked for MS. I wrote network NDIS kernel drivers for a little Finnish company where we were making VPN software with mobile-IP support. That job thought me a lot about C language, Windows and TCP/IP protocol.

    Many times you don’t have to think about how things are done in the low level. You write you SQL scripts with high level T/SQL language (or even higher level LINQ language) and trust that Microsoft’s coders have already optimized everything for you. But in this particular case it is noteworthy to know how things go in the lowest level of Sql Server.

    On the other hand there is a saying that “prematual optimization is the root of all evil”. That means that you shouldn’t think about if SELECT 1 is faster than SELECT * but just concentrate on the big picture and optimize later if needed. While that is true, it doesn’t hurt to learn a bunch of good habits like writing EXISTS(SELECT 1) every time instead of EXISTS(SELECT *)

  2. Second one if you read Brian’s links. I’ll make it short:

    Both of them returns the exact same results so there’s no difference performance wise there. But with the first one server has to expand * to a column list which can and will take some milliseconds.

    And something else that came to mind. Never, ever, use SELECT *. I’ve stumbled in a really confusing situations where you get weird results if you use not-schemabound views. Consider following:

    CREATE TABLE MyTable(Column1 VARCHAR(50))
    GO

    CREATE VIEW v_MyTable AS SELECT * FROM MyTable
    GO

    INSERT INTO MyTable SELECT ‘foo’
    GO

    SELECT * FROM v_MyTable
    – Returns

    – Column1
    – foo
    GO

    ALTER TABLE MyTable ADD Column2 VARCHAR(50)
    GO

    INSERT INTO MyTable SELECT ‘bar’, ‘baz’
    GO

    SELECT * FROM v_MyTable
    – Returns

    – Column1
    – foo
    – bar
    GO

      • Just some idle speculation…

        If you use EXISTS (SELECT *) can optimizer make execution plan out of it? Could it be so that when the query is ran first time it expands * to column list and makes plan form it and on subsequent queries it uses execution plan instead of expanding it again?

        In that case it would really matter what your subquery looks like :)

  3. I have a same kind of scenario to address… If you have a database where you have lots of stored procedures in which you create dynamic tables, we make sure that the table doesnt exists, so we write:

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[tablename]‘) AND type in (N’U’))
    DROP TABLE [tablename]

    The above code will be repeated in many places across many stored procedures as needed.

    Is it better write a function Drop table and and pass table name as parameter to do this? In my opinion, I think it is expensive to go to a function from a stored procedure just to drop a table.

    But, there are people who also think that you can avoid writing the same 2 lines of code in so many places and so call a function.

    Each’s programmers style is different. But how expensive is a function call from a stored procedure compared to IF EXISTS… DROP TABLE statement in stored procedure?

    I really appreciate your comment on this.

    • > But how expensive is a function call

      I don’t think that you even notice it. This is my educated guess about the subject.

      Functions and procedures are (pre)compiled inside the DB, except some cases, so it’s just a case of a simple function call which takes something like n+8 CPU cycles, where n is the amount of parameters (IIRC, it’s been while since I’ve used assembler).

      I’m not sure if DB can even compile simple procedures/functions as inline, but if it can, then there’s no overhead at all.

    • Why bother with

      IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’[tablename]‘) AND type in (N’U’))

      when

      IF OBJECT_ID(N’[tablename]‘, N’U’) IS NOT NULL

      will work?

      It is much simpler to use/implement/read, etc. (IMHO)

  4. “why use a function instead of ‘if exists… drop table’?” a good reason is because DROP TABLE requires the alter table privilege. you may have a situation where a user needs to be able to drop a table, but shouldn’t have the wider-ranging ALTER privilege. solution is to create a function that drops the table, and then grant execute priv on the function to the user.

    i know, i know… if we only could trust our users we wouldn’t have to do through all these security hoops….

  5. Pingback: SQL SERVER – Enumerations in Relational Database – Best Practice Journey to SQL Authority with Pinal Dave

  6. Hello Murli,

    Start a profiler trace to log execution details and get the average CPU time. You can set a filter to log only information about specific query to reduce the logging overhead.
    In SSMS you can get details by setting SHOWPLAN_ALL ON.

    Regards,
    Pinal Dave

  7. Pingback: SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 13 of 31 « Journey to SQLAuthority

  8. Have a query with IF EXISTS.Please help me understand y this happens?
    create table table1(col1 int,col2 int);
    insert into table1 values (2,3)

    If exists(select * from table1 group by col1 )
    print ‘hai’
    GO

    It prints Hai even though the query with in if exists is improper in syntax!!

  9. If exists(select col1,col2 from table1 group by col1 )
    print ‘hai’
    GO

    but this will give me an error,this is wat the compiler will convert the previous query to.

    Shouldn’t this also print ‘Hai’ if not parsed? Pls clarify

  10. Exists will check for existance of the data that comes as the result of the query(with-in Exists) and not the whole table. and the previous query gives me this error
    Column ‘table1.col2′ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

  11. Hi,
    I have 2 tables with addr column. I want a query to display addr from the 1st table and if it is null go to the 2nd table and get the addr value for the corresponding row. What should be the query?

  12. Can you please let me know the relation key between two tables? Meanwhile, I’ve prepared below script. Kindly check SELECT statement in last

    CREATE TABLE Add1 (EmpNo Int, EmpAdd1 NVarChar(50))

    CREATE TABLE Add2 (EmpNo Int, EmpAdd2 NVarChar(50))

    – Insert in Add1 table
    INSERT INTO Add1 (EmpNo, EmpAdd1)
    VALUES(1,’Delhi’)

    – Insert in Add1 table
    INSERT INTO Add1 (EmpNo, EmpAdd1)
    VALUES(2,NULL)

    – Insert in Add2 table
    INSERT INTO Add2 (EmpNo, EmpAdd2)
    VALUES(2,’Yamuna Nagar’)

    – Insert in Add1 table
    INSERT INTO Add1 (EmpNo, EmpAdd1)
    VALUES(3,’Jagadhri’)

    – Insert in Add1 table
    INSERT INTO Add1 (EmpNo, EmpAdd1)
    VALUES(4,NULL)

    – Insert in Add2 table
    INSERT INTO Add2 (EmpNo, EmpAdd2)
    VALUES(4,’Hyderabad’)

    – Check this as per your requirement — this Case statement will help you
    SELECT Add1.EmpNo,
    [Address] =
    CASE
    WHEN
    Add1.EmpAdd1 IS NULL THEN Add2.EmpAdd2
    ELSE
    Add1.EmpAdd1
    END
    FROM
    Add1
    LEFT OUTER JOIN Add2
    ON Add1.EmpNo = Add2.EmpNo

  13. Hi all,

    what is the difference bet. the two query:

    Select * from table where col IS NOT NULL &
    Select * from table where col ‘’

    Thanks in advance.

  14. Arguing over semantics and then making obtuse statements about NULL not existing and VOID(*)=0×0000000 and then NOT taking any memory is silly. By the same token NULL and VOID both are semantical version of the same thing, nothing. Further using the “exists” test fails in the case if the object simply does not exist, the error handling will still be invoked thus even more processor time is used handling that infraction. Even further to assume 4 bytes is always taken up to pad memory is incorrect as this all depends on the processor including any program overrides on byte alignments.

    It is simply better to just query whatever system table desired which eliminates the error handling. This is the quickest way since the example above does not account for error invocation which skews the results.

  15. Pingback: SQL SERVER – Weekly Series – Memory Lane – #017 | SQL Server Journey with SQL Authority

  16. Can you please explain how EXISTS and NOT EXISTS works?

    As per me EXISTS applied after where clause and return which have record and NOT EXISTS removed row if record found.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s