How to check if SQL column has value?

Question

I use Microsoft SQL Server 2012. I need to check if table named SiteObjects in database has at least one record where column named SiteRegionId's value is equal to 22.

If it is it has to imlpement some logic if not it have to implement another logic.

Here my implementation:

IF EXISTS (SELECT * FROM SiteObjects WHERE SiteRegionId = 22)
BEGIN
    ----do what you need if exists
    RETURN '0'; --not deleted
END
ELSE
BEGIN
    --do what needs to be done if not
    DELETE FROM SiteRegions 
    WHERE Id = 22;

    RETURN '1';
END

I think that this row:

SELECT * FROM SiteObjects WHERE SiteRegionId = 22

is not effective because as I understand it runs on all table rows and selects all rows where SiteRegionId is equal to 22. Is there any way to make it more effective? And to check if any of the row meets conditions if it is, proceed.

Answer 1

No, EXISTS is smart enough to return true as soon as it has observed a single row. And the system knows that no column data is actually retrieved.

Back in the 90s some optimizers and database systems weren't smart enough to recognize this situation, but I'm not aware of any these days that would actually retrieve all data. (Certainly, for the SQL Server line, it was fixed back before the 2000 version of the product)

Thus, the suggestions in the other answers are left-over advice from over a decade ago.

See Subqueries with Exists:

The subquery does not actually produce any data

...

• The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no reason to list column names because you are just testing whether rows that meet the conditions specified in the subquery exist.

Answer 2

I would suggest you to write:

IF EXISTS (SELECT 1 FROM SiteObjects WHERE SiteRegionId = 22)

You dont need to use * ie, fetch all the columns of your table to check the existence of a record. You can simply do this using a 1.

However as far as efficiency is concerned both the queries ie., the one using * and the one using 1 will have almost the same execution plan when executed. Usually 1 is preferred to make it more readable. You can follow this link to see how the two queries will give the same execution plan. SQL SERVER – SELECT 1 vs SELECT * – An Interesting Observation