COUNT VS EXISTS


If you want to check wether a rows or rows exists in a table for a given search criteria, which would be better choice between COUNT and EXISTS to check?
And the answer is EXISTS.

How?

When you use COUNT it scans the whole table for the given search criteria.

IF (SELECT COUNT(1) FROM tblEmployee WHERE empName LIKE ‘%najmul%’) >0
BEGIN
            -- Your statement
END


Now if you have 10000 records in the tblEmployee it will look into all the 10000 records. So its time consuming.
But when you use EXISTS then as soon as it finds one record its stops searching. So In this case it don’t scan whole table and hence its fast as compare to COUNT.

IF EXISTS (SELECT ‘Y’ FROM tblEmployee WHERE empName LIKE ‘%najmul%’)
BEGIN
            -- Your statement
END