T-SQL: IF NOT EXISTS versus @@ROWCOUNT | John Papa

John Papa

Evangelist on the loose

T-SQL: IF NOT EXISTS versus @@ROWCOUNT

...

I was asked a good question the other day regarding why I chose to use “IF NOT EXISTS” in my Nov 2003 MSDN article on User Defined Functions to check to see if a declared table variable had any rows in it versus checking @@ROWCOUNT. Mostly I chose to use IF NOT EXISTS because I know that I find it less apt to break on me in case I do something silly. For example, if I chose to check @@ROWCOUNT in the SQL sample code below instead of using “IF NOT EXISTS”, it works just fine. But what happens if I (or someone else) adds another SQL statement at line 15 that alters the @@ROWCOUNT? For example, If I augment this code in the future to inlcude an UPDATE statement at line 15, the ROWCOUNT will no longer represent the number of rows affected by the INSERT statement (lines 10-14). Is this likely to happen? Probably not. Has it happened to me? Oh yeah, I was burnt once on this. Is it bad to use ROWCOUNT? Not at all! I was being overly careful in this code example and also wanted to show how “IF NOT EXISTS” works. So the answer is that both ways work.

 

    1 CREATE FUNCTION fnGetEmployeesByCity3 (@sCity VARCHAR(30))

    2     RETURNS @tblMyEmployees TABLE

    3     (

    4         FirstName VARCHAR(20),

    5         LastName VARCHAR(40),

    6         Address VARCHAR(120)

    7     )

    8 AS

    9 BEGIN

   10     INSERT   @tblMyEmployees

   11     SELECT   FirstName, LastName, Address

   12     FROM     Employees

   13     WHERE    City = @sCity

   14     ORDER BY LastName

   15 

   16     IF NOT EXISTS (SELECT * FROM @tblMyEmployees)

   17         INSERT @tblMyEmployees (Address)

   18             VALUES (‘No matching employees found in the specified city’)

   19 

   20     RETURN

   21 END

tags: All
  • http://codebetter.com/blogs/sahil.malik Anonymous

    Isn’t exists also faster performance viz?

  • http://www.codebetter.com/blogs/raymond.lewallen Anonymous

    Not faster because you actually have to execute it, which performs a semi join. When just checking rowcount, you’re checking a global var that already exists and it populated because of the insert statement. Problem is, that value in @@rowcount can change very easily and break your code, so I think his point, unless I’m misunderstanding this post and your comment, is to have code less likely to be broken.

  • http://codebetter.com/blogs/john.papa Anonymous

    Raymond, That’s the debate, IMO. You nailed it. Do you go for performance and ease of use (@@ROWCOUNT) or do you go with less apt to break (IF NOT EXISTS) … that’s a call that is correct either way, IMO. Really depends on developer choice … I was just answering why I chose the “less likely for Papa to break it” technique :-)

  • http://www.codebetter.com/blogs/raymond.lewallen Anonymous

    John, that’s my way of doing it to. Even if there becomes a noticable performance hit, depending on your WHERE clause, I’d look at a different indexing scenario to help out the EXISTS before I’d rely on @@rowcount.

  • Anonymous

    when I use EXISTS I always specify top 1 and the field name, something like:
    IF NOT EXISTS (SELECT top 1 FieldNameX FROM @tblMyEmployees)

  • http://www.lazycoder.com Anonymous

    Within the transaction of the insert/delete could you set @@ROWCOUNT equal to a local var (e.g. @currRowCount) and use that as your baseline rather than the global var?

  • http://codebetter.com/blogs/john.papa Anonymous

    BlackTigerX … Not a bad idea to use TOP 1.
    Scot … Sure could use a local variable to store the @@ROWCOUNT. I’ve done that before just to be safe that something I did didn’t change the value of @@ROWCOUNT later.
    Good comments

  • Anonymous

    Why even TOP 1? You could always do IF [NOT] EXISTS (SELECT 1 FROM tbl). I’d guess that it’s even less overhead than TOP…though they’re both likely to pretty miniscule compared to the rest of your SP.

  • http://www.mynicemailat.com Anonymous

    Very interesting blog!

  • Anonymous

    To Donny: If you use “SELECT 1 FROM tbl” wont you get all the records of the table with a single column with the value of 1?
    But I think it’s less overhed like you said.

  • Anonymous

    So which is going to be faster within the IF EXISTS statement?
    Select * from table where PK = X
    or
    Select PK from table where PK = X

  • http://www.sqlserver.org.au Anonymous

    “So which is going to be faster within the IF EXISTS statement?
    Select * from table where PK = X
    or
    Select PK from table where PK = X”
    SELECT * is the slower one..always use PK..

  • Anonymous

    Is there a topic on IF EXISTS vs IF NOT EXISTS?
    I have just learnt up that COUNT(*) vs IF EXISTS, the latter the better because COUNT looked through the entire table, where as EXISTS break away when it finds the row.
    My question is NOT makes EXISTS look at the entire table like COUNT?

  • Anonymous

    Hey I want to take out 2 fields from 3 tables…(both of the fields are from 1 table only)
    and want to exclude entries present in a combination of 3 tables again ()
    Currently My query structure is…
    Select A.a, A.b from A, B
    from….
    and A.a||B.b not in (Select A.a||B.b from A, B, C)
    Please let me know how I convert my query to male it use Not Exists…
    Thanks in advance

  • Anonymous

    Guys, there is no difference between
    a) IF EXISTS (SELECT * FROM table)
    b) IF EXISTS (SELECT PK FROM table)
    c) IF EXISTS (SELECT 1 FROM table)
    d) IF EXISTS (SELECT TOP 1 * FROM table)
    The function does not return any data, only a true or false, so it doesn’t matter if you select 1, select pk or select *, the function still only returns true or false.
    Also selecting TOP 1 doesn’t help either. An exists does a table scan and when it finds the first row, it returns true. It doesn’t continue scanning the table.
    The performance of exists is affected only by the where clause you use.

%d bloggers like this: