SQL Server CHECKSUM() Collisions

checksum_collisions.sql

-- The gist of it:
-- Use HASHBYTES('SHA1', @input); instead of CHECKSUM(@input); if you are interested 
-- in using hashes to detect code changes.

-- oops! I deleted too many records
DECLARE @old_proc_definition VARCHAR(MAX) = '
	DELETE FROM dbo.transactions
	WHERE txn_id < 10000000000000000;
';

-- ok fixed
DECLARE @new_proc_definition VARCHAR(MAX) = '
	DELETE FROM dbo.transactions
	WHERE txn_id < 1;
';

-- query checksum is the same
SELECT 
	  old_checksum = CHECKSUM(@old_proc_definition)
	, new_checksum = CHECKSUM(@new_proc_definition)
	, checksums_equal = 
		CASE
			WHEN CHECKSUM(@old_proc_definition) = CHECKSUM(@new_proc_definition)
				THEN 'equal'
			ELSE
				'not equal'
		END
;
GO

-- oops! I was pulling the wrong status
DECLARE @old_proc_definition VARCHAR(MAX) = '
	SELECT status 
	FROM dbo.control_table
	WHERE code = ''le'';
';

-- ok fixed
DECLARE @new_proc_definition VARCHAR(MAX) = '
	SELECT status 
	FROM dbo.control_table
	WHERE code = ''mu'';
';

-- query checksum is the same
SELECT 
	  old_checksum = CHECKSUM(@old_proc_definition)
	, new_checksum = CHECKSUM(@new_proc_definition)
	, checksums_equal = 
		CASE
			WHEN CHECKSUM(@old_proc_definition) = CHECKSUM(@new_proc_definition)
				THEN 'equal'
			ELSE
				'not equal'
		END
;
GO

-- find checksum collisions in stored procedure definitions
WITH checksum_counts AS (
	SELECT 
		  CHECKSUM(OBJECT_DEFINITION(object_id))	AS object_checksum
		, COUNT(*)					AS checksum_count
	FROM sys.objects
	WHERE OBJECT_DEFINITION(object_id) NOT IN (
		  '((0))'
		, '(getdate())'
		, '(newid())'
		, '(newsequentialid())'
		, '(NULL)'
	)
	GROUP BY CHECKSUM(OBJECT_DEFINITION(object_id))
	HAVING COUNT(*) > 1
)
SELECT 
	  OBJECT_NAME(OBJECT_ID)			AS object_name
	, OBJECT_DEFINITION(object_id)			AS object_definition
	, CHECKSUM(OBJECT_DEFINITION(object_id))	AS object_checksum
FROM sys.objects
WHERE CHECKSUM(OBJECT_DEFINITION(object_id)) IN (
	SELECT object_checksum
	FROM checksum_counts
)
ORDER BY object_checksum;
GO