IF EXISTS vs @@ROWCOUNT

Question

IF EXISTS vs @@ROWCOUNT

Anju Renjith

Ten Centuries

Points: 1135
More actions
November 25, 2014 at 10:21 pm

#295384

Comments posted to this topic are about the item IF EXISTS vs @@ROWCOUNT
November 26, 2014 at 3:25 am

This was removed by the editor as SPAM
November 26, 2014 at 4:01 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

peter.row SSCarpal Tunnel Points: 4319 More actions · Answer 2

I answered neither because neither option answers the question posed.

You say you have an identity column, you add 4 rows and want to add a 5th.

Since this is a table variable it only lives in your own scope and so it is not unreasonable to say that the best way is to simply say:

insert into @table (col2) values (5000)

There is no need to check for the existence of a col1 = 5 because you know it doesn't exist.

Come on people - I know you're trying to keep the questions as short as possible but at least be explicit or have a 1 line "assume that ..." so that we don't have to use the multiple choices answers to figure out what the assumptions are.

Bob JH Cullen SSCrazy Points: 2082 More actions · Answer 3

I agree with peter.row - the question was badly worded. Seems an ongoing trend...

Anju Renjith Ten Centuries Points: 1135 More actions · Answer 4

Could you please reword the question and let me know where I went wrong in wording the question.

Thanks for your time

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

IMHO the option "Neither" is correct, because MERGE is likely to outperform options 1 and 2, both of which require a test in some or all cases. Since the poster used table constructor for the sample data, MERGE is available.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bholthuijsen Valued Member Points: 63 More actions · Answer 6

For extra credit, here is the MERGE:

MERGE @Table T

USING (SELECT 5 AS col1, 1000 AS col2) AS V

ON T.col1 = V.col1

WHEN NOT MATCHED

THEN INSERT (col2) VALUES (V.col2)

WHEN MATCHED

THEN UPDATE SET T.col2 = V.col2;

I would prefer this, even though the one of the others may perform better in some cases.

Bob JH Cullen SSCrazy Points: 2082 More actions · Answer 7

Anju, there are a number of ways of wording any question - but it rather depends what aspect of our knowledge you were intending to test.

By including options that test for the existence of a specific row, when no mention was made of inserting anything other than the original four, it leaves you open to the thought that "Well, why offer those options at all, when a simple INSERT will do it? Perhaps there could have been other activity he hasn't told us about".

That in turn suggests a further scenario that invalidates both your options - what if a row had been inserted and then deleted, but a further row with value 1000 inserted? The row with identity value 5 does not exist, so the tests will fail and the row cannot be updated, but the value you need in the table is already there with an identity value of 6. What are your intentions then? Both the options you present will insert a row that, perhaps, you do not need.

I think, therefore, it is better to include the DML in the question and state that WYSIWYG. Then we have a known starting point. I think most people would prefer questions that challenge knowledge, logic and comprehension, but we need all the facts. If too many assumptions as required, then it all turns into what looks like a trick question, and I'm bored with those.

Bob

tripleAxe SSCertifiable Points: 5706 More actions · Answer 8

I guess this question if trying to show us that from a peformance point of view it is better to use the IF EXISTS statement to check for a rows before updating or inserting it rather than updating the table and then doing an insert if the row did not already exist. I agree with peter.row above, in that in this contrived example as you know the row does not already exist then just go ahead and insert the row without doing any checks (before or after).

My question about this would be does is the IF EXISTS still perform better from a performance point of view once we start using tables with millions of rows. I think it will depend upon indexing, data distribution and if the row already exists or not, and possibly other variables I've not thought of.

Joel Ewald SSCertifiable Points: 6120 More actions · Answer 9

I think this really depends on the assumptions made.

If I assume the code is as given then neither would be the option I would go with since the table variable is within current scope and I can clearly see that 5 would not exist and I can simply perform the insert without an existence check.

If I assume ID 5 does exist then I would think the @@Rowcount option would outperform as the update would be performed and then no further processing would be performed after evaluating @@Rowcount. However, this does bring up the problem of a trigger existing that could change @@Rowcount, so that is another assumption based on current codebase, and the addition of a trigger later could break the existing code - Fragile.

If I assume ID 5 may or may not exist then I would say merge is the best choice since between the existence check and our insert another process could perform the insert of ID 5, unless I further assume we have a transaction / isolation level in place to prevent that from occurring.

I am interested to hear other opinions.

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 10

Sorry for the harsh words, but this is absolute rubbish.

The code in option 2 will always do two lookups. One for the EXISTS, and then another one for either the INSERT or the UPDATE. The code in option 1 will do a single lookup if the row is already there (for the UPDATE), and then only do a second action when the row was not there. So on average, this code is 50% faster (assuming an equal chance of the row being new or already present).

The only correct answer is option 1.

The links provided are bogus. They are about a different comparison: no UPDATE involved, and the WHERE clause was not on the primary key.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Ed Wagner SSC Guru Points: 287024 More actions · Answer 11

Yeah, the question was definitely poor.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Skanker Hall of Fame Points: 3059 More actions · Answer 12

I misunderstood what the question was asking so chose neither.

I can see now from the discussion that this was testing other things - however based on just the question i could only choose neither. Sorry as it is probably my inexperience that led to this choice. Thanks to everyone, as ever, for the discussion as this is where I pick up loads. 😉