2
Vote

Column compare method

description

Column compare currently compares every column from source with every column on target (Column by Column).

Using HASHBYTES could be more effective and easier (maybe).

So, implement both compare methods and add new property ColumnCompareMethod: ColumnByColumn or HASHBYTES.

comments

knyazs wrote Apr 23, 2013 at 11:21 PM

Julio's suggestion:

I found that blog post some time ago and I created my own helper function to implement the same logic.
Here you go:

CREATE function [dbo].[udf_GET_HASH_ALL_COLUMNS] (@p_schema_name VARCHAR(50), @p_table_name VARCHAR(255), @p_delimiter_char VARCHAR (1))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @retString as varchar (max)
DECLARE @sqlString as varchar(max)
SET @sqlString = ''


SELECT @sqlString = @sqlString +
CASE DATA_TYPE
WHEN 'int' THEN 'ISNULL(RTRIM(CONVERT(varchar(20),' + COLUMN_NAME + ')),'''')'
WHEN 'datetime' THEN 'ISNULL(RTRIM(CONVERT(varchar(30),' + COLUMN_NAME + ',126)),'''')'
WHEN 'datetime2' THEN 'ISNULL(RTRIM(CONVERT(varchar(30),' + COLUMN_NAME + ',126)),'''')'
WHEN 'date' THEN 'ISNULL(RTRIM(CONVERT(varchar(30),' + COLUMN_NAME + ',126)),'''')'
WHEN 'bit' THEN 'ISNULL(RTRIM(CONVERT(varchar(1),' + COLUMN_NAME + ')),'''')'
WHEN 'decimal' THEN 'ISNULL(RTRIM(CONVERT(varchar('+ CONVERT(varchar(2),NUMERIC_PRECISION) +'),' + COLUMN_NAME + ')),'''')'
ELSE 'ISNULL(LTRIM (RTRIM(' + COLUMN_NAME + ')),'''')'
END + '+ ''' + @p_delimiter_char + '''' + '+'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @p_schema_name and TABLE_NAME = @p_table_name
AND COLUMN_NAME NOT LIKE 'OMD_%'

SET @retString = CASE LEN (ISNULL (@sqlString, '')) WHEN 0 THEN NULL ELSE LEFT(ISNULL(@sqlString, ''), LEN(@sqlString) - 1) END

RETURN @retString
END
GO


I use this function to generate the HASH input string. Then I store the HASH as a string value


CONVERT (VARCHAR(100), HASHBYTES ('SHA1',
[Here you put the string returned by the function]
, 2) AS CHECKSUM


Maybe you can reuse this in your app.

MikeHoney wrote May 7, 2013 at 2:11 AM

Personally I would "down-vote" this one - hashing is inherently risky and I imagine this would be quite tricky to implement in the context of this Wizard.

knyazs wrote Jun 4, 2013 at 2:09 PM

Mike,

can you be more precise when you are saying "hashing is inherently risky"? I know CHECKSUM can be problematic but HASHBYTES should do it's job. You can read more about successful HASHBYTES compare method here: http://www.bidn.com/blogs/TomLannen/bidn-blog/2265/using-hashbytes-to-compare-columns.

On the other hand, creating the TSQL script should not be tricky, believe me ;)

Cheers,