public
Created

SQL Server CHECKSUM() Collisions

  • Download Gist
checksum_collisions.sql
SQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85
-- 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

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.