CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jasmine
Jasmine

Reputation: 5326

Difference between if exists (select 1 from) and (select 0 from)

Is there any difference at all select 1 from and select 0 from or select any integer from?

I know select * from is a expensive operation.

However, select 1 from differs from select 0 from and or select 29 from?

In the context of just checking if a table/stored procedure exists and if exists, drop it and recreate (In these scenarios)

Upvotes: 2

Views: 13157

Answers (1)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

Your performance would be identical. When you use exists, SQL Server doesn't evaluate anything in the SELECT portion of the statement. It simply resolves to a boolean. This can be proven by doing 1 divided by 0. Normally this would throw an error, but inside an EXISTS, it runs without error.

With regards to your specific use, the expense in the EXISTS check is almost nothing. If you are running into performance issues, it's not the EXISTS part of your script.

Example:

IF EXISTS(SELECT 1/0 FROM <TABLE> WHERE 1=1) BEGIN SELECT 'In the exists' END

Upvotes: 8

Related Questions