September 11, 2013 at 1:59 am
Hi,
is there a difference in terms of performance?. The execution plans are identical. Does an index on or off, an index be used? Is there a rule what syntax should be used when?
Regards
Nicole 😉
---------------------------------
IF EXISTS ( SELECT *
FROM Northwind.dbo.Customers
WHERE CustomerID = 'ALFKI' )
PRINT '1'
ELSE
PRINT '0'
---------------------------------
IF EXISTS ( SELECT 1
FROM Northwind.dbo.Customers
WHERE CustomerID = 'ALFKI' )
PRINT '1'
ELSE
PRINT '0'
---------------------------------
IF EXISTS ( SELECT COUNT(1)
FROM Northwind.dbo.Customers
WHERE CustomerID = 'ALFKI' )
PRINT '1'
ELSE
PRINT '0'
---------------------------------
IF EXISTS ( SELECT TOP(1) CustomerID
FROM Northwind.dbo.Customers
WHERE CustomerID = 'ALFKI' )
PRINT '1'
ELSE
PRINT '0'
September 11, 2013 at 2:58 am
These are all same in my opinion except your 3rd query( It will always print an "1" as an output which is not what you want )
You can check the article referenced below for some more insight into this
http://sqlinthewild.co.za/index.php/2011/04/05/to-top-or-not-to-top-an-exists/
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 11, 2013 at 4:35 am
The first two are pretty much the same and can probably be used interchangebly, depending on the actual complexity of the query involved. The last two are very different. COUNT is an aggregation function and will, in most cases, probably be much more expensive. TOP should always be given an ORDER BY to ensure appropriate order. But, for this hyper-simple example, it probably works in a similar fashion to the first two, but as you add complexity, it will change it's behavior.
If you're just doing a regular EXISTS statement, I usually use the SELECT * syntax.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply