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.
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)?
@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.
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
“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)
43 Comments. Leave new
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 *)
Can we use following….
IF EXISTS (Select top (1) * from table)
Instead of below query
IF EXISTS (Select 1 from table)
Which will be better a perfomance wise?
There wont be a difference.
If table having one million row in case of second option?
The EXISTS operator (as well as IN, INTERSECT etc) implements a semi-join operator which, effectively, does the same thing. The operator terminates as soon as it finds a single matching row.
Sometimes, the optimizer will rewrite a LEFT OUTER JOIN in this way if you are then filtering by NULL/NOT NULL but not always.
i think it make big difference if you select *.
because * contain the number of items. it required time to fill that item.
regards
Ananta
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
“and will take some millisecond”
I doubt even that long.
“Never, ever, use SELECT *.”
…outside of an EXISTS subquery. :)
BTW, your Pinal-quoted comment is excellent.
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 :)
@Marko
I do not know. I only know that is does expand it, based on the post mentioned above.
In that case, the metadata for the view needs to be refreshed using sp_refreshview.
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.
Passing a table name to drop it requires dynamic sql which can’t bed used inside a function
> 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)
“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….
You can’t use a Funtion to drop a table
You can only use stored procedure for this purpose
Hi Pinal,
I have a query that is i want to calculate the execution time of a query. how do i will get ?
You can use
SET STATISTICS TIME ON
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
What is the difference between if exists (select * from .. ) and if exists (select 1 from .. ) ?
There is no difference becuase it is used to check the existance of the record and no results are returned
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!!
The exists just checks the existance of the data in the table. It will not parse entrire statement for correctness
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
Exists is used to check the existance of the data in the table. Group by clause will be omitted
doesnt that mean the group by clause is not omitted for Exists
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.
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?
select coalesce(t1.address,t2.address) as address from table1 as t1 inner join table2 as t2 on t1.col=t2.col
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
Hi,
ORA-00923: FROM keyword not found where expected
Please Check this and send the correct formation …!
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.
Both have different meaning. One checks for NULL values while another checks for emptry strings
Arguing over semantics and then making obtuse statements about NULL not existing and VOID(*)=0x0000000 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.
Would an Index improve the performance of IF EXISTS (SELECT *…) as compared to IF EXISTS (SELECT 1…) ?
Can we write a view like
CREATE VIEW
AS
IF EXISTS()
BEGIN
SELECT STATEMENT
END
ELSE
BEGIN
SELECT STATEMENT
END
select 1 from table , will execute faster since there query specifies no columns to extract
gerald is right. and simplest way to explain.
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.