EXISTS vs. COUNT in TSql

Sometimes, programmers will perform a something like the below in TSql:

DECLARE @CountSum int
SET @CountSum = (SELECT COUNT(COL1) FROM TABLE WHERE ......)
IF @CountSum = 0
BEGIN
//Perform some TSql operations..........
END

with this code, you are really looking for the existense of records that match a criteria.  If that's the case, an alternative would be to use EXISTS below

DECLARE @CountSum int
IF EXISTS(SELECT COL1 FROM TABLE WHERE.......)
BEGIN
//Perform some TSql operations..........
END

I just did a fairly quick performance test and I found that in my test scenario, the EXISTS code was a little bit faster, even with a small bit of data.  As the amount of data increases, I think the performance difference should increase.

Wally

6 Comments

  • Thanks for the tip. But what if I want to retrieve the row that exists and assign it to variables or perform a computation:



    IF EXISTS (SELECT * FROM Customers WHERE A=1)

    BEGIN

    --get row that exists and assign to

    --variables

    SET @CustomerName = ?

    SET @CustomerId = ?

    SET @CustomerFullName = ?

    END



    Thanks

  • If you want just that, do something like this



    SELECT TOP 1 @CustomerName=Name,@CustomerId=Id,@CustomerFullName=FullName FROM Customers WHERE A=1

    IF @@ROWCOUNT=1

    BEGIN

    ...

    ...

    ...

    END

  • Well, i tried this example with a table having around 5000 records and the query execution shows the same performance and time

  • Nairooz, you will need to add a few more zeroes to the number of records you have before you will see a performance difference. Try it again with 5,000,000 records and you will most likely see a difference.

  • Thanks for this usefull information

  • Well, Exists should be faster.
    It should return as soon as it find any data,
    Count should work more to find the number of rows.
    Unless Tsql is clever and understand that you want to check if the count=0 meaning there are no data.

Comments have been disabled for this content.