SQL Server Performance

Checking Existance of a record

Discussion in 'T-SQL Performance Tuning for Developers' started by akashenk, May 3, 2007.

  1. akashenk New Member

    Can anyone tell me if this is the best way (performance-wise) to check for the existence of a record in a table given certain criteria:

    SELECT TOP 1 'True' FROM mytable WHERE myCriteriaExpression

    Example: SELECT TOP 1 'True' FROM dbo.[Orders] WHERE customer_id='123'

  2. Adriaan New Member

    The best option is IF EXISTS:

    IF EXISTS (SELECT T.CustomerID FROM db:confused:rders T WHERE T.CustomerID = @CriteriaString)
    BEGIN
    ...............
    END

    The EXISTS keyword is like the TOP 1 clause: the query stops after one match is found. This way it doesn't really matter if your criteria make up a unique key or not.

    I would guess your plan was to use something like this:

    SELECT TOP 1 'True' FROM mytable WHERE myCriteriaExpression
    IF @@ROWCOUNT > 0
    BEGIN
    ................
    END

    ... but I think the IF EXISTS syntax makes the flow of the procedure somewhat easier to follow.
  3. ndinakar Member

    IT doesnt matter what you have in your SELECT list. SQL Server will exit out of the condition as soon as it finds the first row that matches your condition in your "myexpression". So you can put TOP 1, &lt;column list&gt; or 1. Its all the same.<br /><pre id="code"><font face="courier" size="2" id="code"><br />IF EXISTS ( SELECT * FROM yourTable WHERE &lt;Condition&gt<img src='/community/emoticons/emotion-5.gif' alt=';)' /><br />BEGIN<br /> ......<br />END<br /></font id="code"></pre id="code"><br /><br />***********************<br />Dinakar Nethi<br />SQL Server MVP<br />***********************<br /<a target="_blank" href=http://weblogs.sqlteam.com/dinakar/>http://weblogs.sqlteam.com/dinakar/</a>
  4. Adriaan New Member

    It usually doesn't hurt to name a specific column. I have seen problems in triggers with text columns, where the * would fail. Better safe than sorry.
  5. akashenk New Member

    Thanks for the replies, everyone. What I am actually looking for is a stand-alone SP. In other words, I don't need to check for the existence of a record as part of a larger SP. THis is why I'm not sure EXISTS applies.

  6. satya Moderator

    You can embed these statements on to a Stored procedure and run that based on your criteria, your questions sounds like a classroom exercise.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  7. satya Moderator

    You can embed these statements on to a Stored procedure and run that based on your criteria, your questions sounds like a classroom exercise.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page