Cost Analysis Among EXISTS (SELECT 1), EXISTS (SELECT COUNT(1)), EXISTS (SELECT *) AND EXISTS (SELECT TOP 1)

Let's understand how 'SELECT 1', 'SELECT COUNT(1)', 'SELECT *' and 'SELECT TOP 1' would behave when they are called in the WHERE EXISTS clause. Let's look at following queries and find out which one will cost least.

Query 1:
SELECT a.ProductId, a.ProductName
FROM Product.Catalog AS a
WHERE EXISTS
(SELECT *
FROM Product.Products AS b
WHERE a.ProductId = b.ProductId
AND a.ProductName = 'RAM');
GO

Query 2:
SELECT a.ProductId, a.ProductName
FROM Product.Catalog AS a
WHERE EXISTS
(SELECT 1
FROM Product.Products AS b
WHERE a.ProductId = b.ProductId
AND a.ProductName = 'RAM');
GO

Query 3:
SELECT a.ProductId, a.ProductName
FROM Product.Catalog AS a
WHERE EXISTS
(SELECT TOP 1
FROM Product.Products AS b
WHERE a.ProductId = b.ProductId
AND a.ProductName = 'RAM');
GO

Custom Search

Query 4:
SELECT a.ProductId, a.ProductName
FROM Product.Catalog AS a
WHERE EXISTS
(SELECT COUNT(1)
FROM Product.Products AS b
WHERE a.ProductId = b.ProductId
AND a.ProductName = 'RAM');
GO

There is a grave misconception among DBAs and I saw most of them choosing query 3 and 4. The clue is that work under 'WHERE EXISTS' completes as soon as the first record is returned. Query 1 and 2 are same here. Now Query 3 is more costlier than the first two and query 4 is most costly. Also query 4 is nowhere equivalent to others as this will always execute even this returns 0.

We can arrange the queries Cost wise: query 1 = query 2 < query3 < query 4

Regards,
http://tuitionaffordable.webstarts.com