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:

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

Technically, however, * does get expanded, adding some minuscule amount of time:

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 (https://blog.sqlauthority.com)