SqlServer Upsert

Updating a record or inserting it (if it didn't exist) is a common optimization.

Simple (not concurrent-safe)

UPDATE then check @@ROWCOUNT and INSERT if necessary.

DECLARE @CategoryName NVARCHAR(15) = 'Dairy';
DECLARE @Description NVARCHAR(MAX) = 'Milk, cheese and yoghurts';
DECLARE @Id int = null;

UPDATE [Categories]
	SET [Description] = @Description,
		@Id = [CategoryID]
	WHERE [CategoryName] = @CategoryName;

IF @@ROWCOUNT = 0
	INSERT INTO [Categories]
			   ([CategoryName]
			   ,[Description])
		 VALUES
			   (@CategoryName
			   ,@Description);
--if id is not set in UPDATE, then grab scope identity
SET @Id = ISNULL(@Id, CAST(SCOPE_IDENTITY() AS int));
--select it out
SELECT @Id AS Id;

This example grabs the affected Id too (whether identity insert or update).

Concurrent-safe

A more conventional IF NOT EXISTS... INSERT - ELSE - UPDATE. The lock hints protect for concurrency. The UPDLOCK and SERIALIZABLE hints are as suggested in this Sam Saffron blog post from 2007

UPDLOCK - prevent dirty reads
HOLDLOCK or SERIALIZABLE - Hold a shared lock until completion of the transaction
ROWLOCK - row level locking
READPAST - don't block waiting for a rowlock, go to the first unlocked row - select only

IF NOT EXISTS(
		SELECT * FROM [Categories] WITH ( UPDLOCK, SERIALIZABLE ) 
		WHERE [CategoryName] = @CategoryName )
	BEGIN
		INSERT INTO [Categories]
				   ([CategoryName]
				   ,[Description])
			 VALUES
				   (@CategoryName
				   ,@Description);	
		SET @Id = CAST(SCOPE_IDENTITY() AS int);
	END
ELSE
	BEGIN
		UPDATE [Categories] 
			SET [Description] = @Description,
				@Id = [CategoryID]
		WHERE [CategoryName] = @CategoryName;
	END

SELECT @Id AS Id;

MERGE

Much the same as before, using the MERGE command. MERGE by itself is not concurrent-safe; you must still use lock hints.

MERGE INTO [Categories] WITH  ( UPDLOCK, SERIALIZABLE ) AS target
	--if/where part
	USING 
		(SELECT @CategoryName, @Description ) AS source 
			([CategoryName], [Description])
		ON (target.CategoryName = source.CategoryName)
	--found, so update
	WHEN MATCHED THEN
		UPDATE SET [Description] = @Description,
				@Id = [CategoryID]
	--not found, so insert
	WHEN NOT MATCHED THEN
		INSERT ([CategoryName]
				   ,[Description])
			 VALUES
				   (@CategoryName
				   ,@Description);

SET @Id = ISNULL(@Id, CAST(SCOPE_IDENTITY() AS int));

SELECT @Id AS Id;

static void