T-SQL Update or Insert Logic -

Jeremiah Clark's Blog - SQL: If Exists Update Else Insert

"This is a pretty common situation that comes up when performing database operations.  A stored procedure is called and the data needs to be updated if it already exists and inserted if it does not.  If we refer to the Books Online documentation, it gives examples that are similar to:

IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
    UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
    INSERT INTO Table1 VALUES (...)

This approach does work, however it might not always be the best approach. ...

...

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
    INSERT INTO Table1 VALUES (...)

The saved table/index scan can increase performance quite a bit as the number of rows in the targeted table grows.

..."

Interesting... This is an approach I would not have thought of, and it's not something that I don't know I would have ever thought of. It just doesn't "fit" in my developer mind set.

But now that I see it, it makes sense. It's something I'm going to have to think about and test anyway. I've got a few databases/tables with 80+ million rows, so a small improvement could lead to big results.