Skip to content

Instantly share code, notes, and snippets.

@nchammas
Created September 30, 2011 18:33
Show Gist options
  • Save nchammas/1254595 to your computer and use it in GitHub Desktop.
Save nchammas/1254595 to your computer and use it in GitHub Desktop.
SQL Server CHECKSUM() Collisions
-- 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment