5

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.

Improve this question

3 Answers 3

Reset to default
5

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.
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

So there no difference between this two: IF EXISTS (SELECT 1 FROM SiteObjects WHERE SiteRegionId = 22) IF EXISTS (SELECT * FROM SiteObjects WHERE SiteRegionId = 22) ???
@Michael - if you compare plans, you should find that they're identical.
3

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

Improve this answer

1 Comment

I don't see how 1 versus * can be argued about readability. When I see SELECT 1 or SELECT TOP 1, my immediate thought is "this person learnt their SQL in the 90s and hasn't updated their knowledge since (i.e. it was a workaround for a problem that hasn't existed for a long time)"
1

If you just need to know if there are ANY rows where the SiteRegionId = 22, then change your query to SELECT TOP 1 id FROM SiteObjects WHERE SiteRegionId = 22

Replace id with an indexed column (your primary key column), and this will tell you if there are any rows at all, but will do so by bringing back just one column from one row.

TheTOP 1 part stops the query after 1 row has been found, so it doesn't need to look across the whole table.

Improve this answer

1 Comment

EXISTS stops the query after 1 row has been found. It doesn't need any help from a TOP.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.