Check function: Often walk along the river, where not wet shoes

In SQL Server 2012, there are 2 check functions: Check_sum and Binary_check, in some data table, if there are multiple string fields, and the string is very long, if the comparison string is the same, use the check function to convert it to int type , which can improve the speed of data comparison. But do not ignore the caution of CSDN official documents: Using a checksum function, sometimes it is not possible to detect updates to the data unless application can tolerate occasional lost updates. Once the officer function is used in the actual project, you will find that not infrequently, but often the exception that does not detect the data update occurs:

Binary_checksum (*) would return a different value for more, but not all, changes to the row, and can is used to detect most Row modifications.

However, there is a small chance that the checksum won't change. For this reason, we does not recommend using CHECKSUM to detect whether values has changed, unless your application can tol Erate occasionally missing a change.

Due to a fluke, the string field is compared using a check function in the project, which is roughly the following: the url,why and Description fields are very long string fields, especially description, with an average length of 1163 characters.

    as as S     on t.id=s.idwhen matched       and binary_checksum (t.url,t.why,t.description)            <>binary_ Checksum (s.url,s.why,s.description) ....

Most of the time, the function works, but, often in the river Walk, can not wet shoes, in a weekend, leading a text message, I came back to work overtime, the calibration function can not detect the updated data, resulting in data synchronization problems, scorecard and performance impact, must be modified in a timely manner.

, the Description field of Source and target is inconsistent, but the checksum function: Binary_check (url,description,why) returns the same value, which is the same as the lesson, in order to avoid later data updates are lost unexpectedly, Instead of using the checksum function in the ETL, we decided to use the string to do the comparison directly.

After several attempts to find that the check function is more prone to update loss on short strings, it is strongly recommended that you do not use the check function in your project in view of these drawbacks of the checksum function. Direct use of strings to compare, ensure foolproof, compared to data update error, data synchronization slightly slower, more tolerable.

Reference to "Check function: Checksum and binary_checksum":

In a long string, the direct comparison will be slow, the use of checksum comparison speed is relatively high, but binary_checksum and checksum produce a lower quality of the checksum, it is recommended that you do not use the check function in the project. When the string is short, it is very likely that a hash conflict will occur, for example: The following script returns two rows of data, with the same result for each row of two column, compared with checksum, which is better than the binary_checksum effect.

--Database collation is SQL_Latin1_General_CP1_CI_ASSelect Checksum (N‘us', N‘FL', N‘Land O Lakes'), checksum (N‘us', N‘FL ", N land O " Lakes ) select binary_checksum (N "GB  ", N c6 ', N ' bude "), Binary_ Checksum (N ' no ", n< Span style= "color: #ff0000;" > ", N ' myre"   

Check function: Often walk along the river, where not wet shoes