User Id :    Password :      New Member   Forgot Password
 
COUNT VS EXISTS in SQL SERVER
Description 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.   No. of Views     1568
  Rating     5
Author Najmul Hoda   Posted On     12 Aug 2011
Tags SQL SERVER    

Sample Code   Download Code

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

 

 

About Author

About Author He is a .Net developer having 4+ years of experience. He loves writting technical article for .Net developers. Najmul Hoda
No Photo
 
Country India
Company BirlaSoft
Home Page www.dotnetlogix.com

Rate this article

Rating options from poor, fair, good, very good to excelent.  
 

Comments

     
Write your comment here.  
Comment
Verification Code   
  
    
 
Section sponsored by