Log Shipping – NORECOVERY vs. STANDBY mode

On some occasions we need to update an entry in a table in case there is an entry otherwise we need to insert that data into the table. The easiest way to do this is something like this

IF EXISTS ( SELECT customer_id
            FROM   dbo.customer_comments
            WHERE  customer_id = @customerId ) 
	UPDATE  dbo.customer_comments
	SET     customer_comment = @comment +  'something here' 
	WHERE   customer_id = @customerId
ELSE 
	INSERT  INTO dbo.customer_comments( customer_id, customer_comment )
	VALUES  ( @customerId, @comment )

 

If you check the code you can see that for an update, sql server has to go thru the table two times, one for checking whether an entry exists and if exists, it still need to find the same location again to do the UPDATE

Now check the following code,

UPDATE dbo.customer_comments 
SET customer_comment= @comment + 'something here' 
WHERE customer_id = @customerId
IF @@ROWCOUNT = 0 
      INSERT INTO dbo.customer_comments (customer_id, customer_comment) 
      VALUES (@customerId, @comment)

It directly try to update the table, and checks for the value of @@ROWCOUNT before it performs the INSERT operation; (Remember @@ROWCOUNT holds the number of rows affected by the LAST statement ). This way we will be able to save one table access for performing the UPDATE operation; 

There won’t be any performance difference for the ‘insert’ operation.