SQL Server Performance

EXISTS vs. COUNT in TSql

Discussion in 'Contribute Your Performance and Clustering Tips' started by dineshasanka, May 3, 2005.

  1. dineshasanka Moderator

    Most of programmers are using count to check the data existence. Performance can be increased if you can avoid count and replace them with following logic



    If Exists(Select COL1 From TABLE Where.......)
    BEGIN
    --Your code goes here
    END


    However, the problem will occur if you need those values already in that record.

    in that case you can use


    Select TOP 1 @Name=Name, @Id=Id FROM Members WHERE A=1
    IF @@ROWCOUNT=1
    BEGIN
    --Your code goes here
    END

  2. Madhivanan Moderator

    This seems to be good approach.
    But usually to check the existance of the record, I use

    select top 1 1 from table where column='somevalue'
    if @@rowcount=1 then
    Begin
    ---
    End

    To retrive only the table structure, I use

    Select * from tablename where 1=0


    Madhivanan

    Failing to plan is Planning to fail
  3. merrillaldrich New Member

    Looks like Mr. match55 is an advertising robot, and needs shutting down <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  4. Luis Martin Moderator

    Yeap, I allready mail to Brad.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    One of the symptoms of an approaching nervous breakdown is the belief that one's work is terribly important
    Bertrand Russell


    All postings are provided “AS IS” with no warranties for accuracy.



  5. satya Moderator

    I've deleted nearly 9 threads related to the same posting by 3 different user names, I believe we need to be vigilant to avoid any sort of confusion.

    sock83 - ip address - 80.178.183.245
    match 55 - ip address - 80.178.183.245
    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  6. FrankKalis Moderator

    Deleted another two threads. What about banning this user from the forum and the IPs from the webserver?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  7. bradmcgehee New Member

    I deleted the account and banned the IP address.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  8. satya Moderator

    I feel that was more appropriate than my actions in move other 2 posts [<img src='/community/emoticons/emotion-1.gif' alt=':)' />].<br /><br /><hr noshade size="1"><b>Satya SKJ</b><br />Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com/forum>http://www.SQL-Server-Performance.Com/forum</a><br /><center><font color="teal"><font size="1">This posting is provided “AS IS” with no rights for the sake of <i>knowledge sharing.</i></font id="size1"></font id="teal"></center>

Share This Page