COUNT(*) Versus IF EXISTS.

This style of coding is often followed by many developers where in we check whether records are present in table by using SELECT * or COUNT(*).But the question is are we using the
right method to instead for just checking existence of records and the answer is no.

For a developer it is nice to see records in table for verification but it would be still very nicer if one has followed the highly efficient EXISTS clause from a performance stand point, which performs many a times better compared to COUNT(*) as the execution chain breaks up as soon it locates a value but in COUNT(*) it scans the entire table to find the count of records which would prove to be costly and not satisfying our need

As always let us see an example to understand this better:

SET NOCOUNT ON
SET STATISTICS IO OFF

IF EXISTS ( SELECT 'X' FROM sysobjects where TYPE = 'U' and name = 'TEST_COUNT_EXISTS')
DROP TABLE TEST_COUNT_EXISTS
GO

CREATE TABLE TEST_COUNT_EXISTS
( DOC_ID INT IDENTITY(1,1) NOT NULL,
DOC_NAME VARCHAR(255) NOT NULL,
LANG_ID INT NOT NULL,
DOC_PATH VARCHAR(2000) NOT NULL
)

CREATE INDEX IX_LANG_ID ON TEST_COUNT_EXISTS(LANG_ID)

DECLARE @iCount INT
SELECT @iCount = 1

--Populate Table with 100000 rows
WHILE @iCount < = 100000
BEGIN
INSERT INTO TEST_COUNT_EXISTS(DOC_NAME,LANG_ID,DOC_PATH)
SELECT 'DOC_NAME' + CONVERT(VARCHAR,@iCount), 1 , '\\MYCOMP\PATH\' + CONVERT(VARCHAR,@iCount)
SELECT @iCount = @iCount + 1
END

--Make the sample random by using GUID
UPDATE TEST_COUNT_EXISTS
SET LANG_ID = 1
WHERE DOC_ID IN ( SELECT TOP 10000 DOC_ID FROM TEST_COUNT_EXISTS ORDER BY NEWID())

UPDATE TEST_COUNT_EXISTS
SET LANG_ID = 2
WHERE DOC_ID IN ( SELECT TOP 10000 DOC_ID FROM TEST_COUNT_EXISTS ORDER BY NEWID())

UPDATE TEST_COUNT_EXISTS
SET LANG_ID = 3
WHERE DOC_ID IN ( SELECT TOP 10000 DOC_ID FROM TEST_COUNT_EXISTS ORDER BY NEWID())


SET STATISTICS IO ON

IF (SELECT COUNT(*) FROM TEST_COUNT_EXISTS WHERE LANG_ID = 3) > 0
SELECT 'COUNT(*)'

IF EXISTS (SELECT * FROM TEST_COUNT_EXISTS WHERE LANG_ID = 3)
SELECT 'EXISTS'

/* Output
Table 'TEST_COUNT_EXISTS'. Scan count 1, logical reads 141, physical reads 0, read-ahead reads 31, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TEST_COUNT_EXISTS'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

/* Output

By the above you we can infer that its EXISTS performs better when compared to a COUNT(*).