We have a procedure that maintains adding standard audit triggers to all our tables. Basically the trigger updates the record with certain demographics of the last user that modified the record (host, user, datetime). This job basically cycles all tables, generates dynamic SQL for each table for this trigger, grabs the definition for the existing trigger and performs a BINARY_CHECKSUM to see if the values are differnet.With this in mind, I have ran across a situation when both strings contain spaces, line feeds and carriage returns the BINARY_CHECKSUM returns the same value for both - even though a minor difference in case exists. However, remove the spaces, line feeds and carriage and the BINARY_CHECKSUM returns different values. Below is a sample:DECLARE @VarA NVARCHAR(MAX);DECLARE @VarB NVARCHAR(MAX);DECLARE @VarC NVARCHAR(MAX);DECLARE @VarD NVARCHAR(MAX);SET @VarA =' CREATE TRIGGER [dbo].[t_upd_TestTable_001] ON [dbo].[TestTable] FOR UPDATE AS UPDATE SRC SET UserModified=LEFT(SUSER_SNAME(),20), DateModified=GETDATE(), HostModified=LEFT(HOST_NAME(),20) FROM inserted AS INS INNER JOIN [MYDatabase].[dbo].[MyTable] AS SRC ON INS.[KeyField]=SRC.[KeyField];'SET @VarB =' CREATE TRIGGER [dbo].[t_upd_TestTable_001] ON [dbo].[TestTable] FOR UPDATE AS UPDATE SRC SET UserModified=LEFT(SUSER_SNAME(),20), DateModified=GETDATE(), HostModified=LEFT(HOST_NAME(),20) FROM inserted AS INS INNER JOIN [MyDatabase].[dbo].[MyTable] AS SRC ON INS.[KeyField]=SRC.[KeyField];'SELECT BINARY_CHECKSUM(@VarA), BINARY_CHECKSUM(@VarB)/* Both return -917347571 */SET @VarC = REPLACE(@VarA, ' ', '')SET @VarC = REPLACE(@VarC, CHAR(13), '')SET @VarC = REPLACE(@VarC, CHAR(10), '')SET @VarD = REPLACE(@VarB, ' ', '')SET @VarD = REPLACE(@VarD, CHAR(13), '')SET @VarD = REPLACE(@VarD, CHAR(10), '')SELECT BINARY_CHECKSUM(@VarC), BINARY_CHECKSUM(@VarD)/* @VarC returns 332712860, @VarD returns 334810012 */
Any ideas why this is? Or any better suggestions for determining if the two statements are identical?
Comparison using BINARY_CHECKSUM question
Posted - 2008-08-29 : 10:42:12
