Combining MERGE with EXISTS and EXCEPT

By combining EXISTS with EXCEPT you can quickly and efficiently compare whether two columns are equal (including when they are both NULL). Using this method if the column on both sides is NULL then they will be treated as equal; and if one side is NULL and the other not, they will be correctly treated as not equal.

Example:

SELECT ... FROM ...
WHERE EXISTS( SELECT [Column_A] EXCEPT SELECT [Column_B] );

The biggest power of this is realised when combining it with the MERGE statement. By using AND EXISTS(SELECT ... EXCEPT SELECT ...) within the WHEN MATCHED ... portion of the merge, SQL automatically checks for NULL’s on both sides. This is more performant than using the not equal to operator with ISNULL e.g. ISNULL(...,'') <> ISNULL(...,''), as SQL can now take advantage of any indexes that exist.

MERGE INTO [TargetTable] AS [T]
USING [SourceTable] AS [S]
	ON ([T].[ID] = [S].[ID])
WHEN MATCHED AND EXISTS (
	SELECT
		 [T].[Code]
		,[T].[Description] COLLATE Latin1_General_CS_AS
	EXCEPT SELECT
		 [S].[Code]
		,[S].[Description] COLLATE Latin1_General_CS_AS
) THEN
	UPDATE SET
		 [T].[Code] = [S].[Code]
		,[T].[Description] = [S].[Description]
WHEN NOT MATCHED BY TARGET THEN
	INSERT ([ID], [Code], [Description])
	VALUES ([ID], [Code], [Description])
WHEN NOT MATCHED BY SOURCE THEN	DELETE;