Menu
Is there a way to md5sum a row in a SQL table to check whether any column has been modified?
I would like to check whether any particular column has been changed versus an old copy of the data which i hold in a text file (which I will md5sum in C#).
EDIT: Just md5sum-ing each row
355
This is how it can be done via a select statement: SELECT Pk1 ,ROW_NUMBER() OVER ( ORDER BY Pk1 ) 'RowNum' ,(SELECT hashbytes('md5', ( SELECT Pk1, Col2, Col3 FOR XML raw ))) 'HashCkSum' FROM [MySchema]. [MyTable]; where Pk1 is the Primary Key of the table and ColX are the columns you want to monitor for changes.
Supported algorithmsMicrosoft SQL Server has supported the same hashing values from Microsoft SQL Server 2005 to Microsoft SQL Server 2008 R2. You can use MD2, MD4, MD5, SHA, or SHA1 to create hashes of your data. These algorithms are limited up to 20 bytes only.
The hashing algorithm hashes the primary index value and returns a 32 bit number, called Row Hash. The higher order bits of the row hash (first 16 bits) is used to identify the hash map entry. The hash map contains one AMP #.
To select rows using selection symbols for character or graphic data, use the LIKE keyword in a WHERE clause, and the underscore and percent sign as selection symbols. You can create multiple row conditions, and use the AND, OR, or IN keywords to connect the conditions.
There are CHECKSUM(*), BINARY_CHECKSUM(*) and CHECKSUM_AGG. They do CRC32 like checkum, but for detecting changes to a row it should be more than enough (you are talking about 1 in 4 billion chances for a false negative collision).
Doing a cryptographic hash using HASHBYTES requires you to construct an expression representing the 'row'.
65
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
