I was glad to see on mssqltips a script to generate column list for table in order to by use in hashbytes function later for table comparison : article with original get_hash_field function
But as often I needed to do more than just concatenate all fields. My need is :
- insert a symbol between field to avoid (or limit) the "birthday paradox" : avoid to obtain the same final string if you concat "123" and "45" or you concat "12" and "345"
- replace null field with a dummy string 'µ' for me
- be able to exclude 2 lists of column for build the list of fields : I wanted all columns of the table except 1 or more fields nor fields in the key that was not needed for me. In the orginal function this list was static
For that I use store procedure instead of function and dynamic sql was mandatory (for the parameters @p_exception_column_list and @p_key_column_list that are strings with quotes) :
CREATE procedure [dbo].[sp_get_hash_fields2] (@p_table_name VARCHAR(100),@p_schema_name VARCHAR(20), @p_exception_column_list nvarchar(2000), @p_key_column_list nvarchar(2000), @sqlStringOut nvarchar(max) output) AS BEGIN --DECLARE @sqlString as nvarchar(max) DECLARE @sqlStringtmp as nvarchar(max) declare @sqlStatement as nvarchar(max) --SET @sqlString = '' set @sqlStatement= N' --DECLARE @sqlString varchar(max) SET @sqlString=''''; SELECT @sqlString = @sqlString + CASE DATA_TYPE WHEN ''int'' THEN ''ISNULL(RTRIM(CONVERT(varchar(10),'' + COLUMN_NAME + '')),''''µ'''')'' WHEN ''bigint'' THEN ''ISNULL(RTRIM(CONVERT(varchar(20),'' + COLUMN_NAME + '')),''''µ'''')'' WHEN ''smallint'' THEN ''ISNULL(RTRIM(CONVERT(varchar(10),'' + COLUMN_NAME + '')),''''µ'''')'' WHEN ''tinyint'' THEN ''ISNULL(RTRIM(CONVERT(varchar(10),'' + COLUMN_NAME + '')),''''µ'''')'' WHEN ''datetime'' THEN ''ISNULL(RTRIM(CONVERT(varchar(20),'' + COLUMN_NAME + '',120)),''''µ'''')'' WHEN ''datetime2'' THEN ''ISNULL(RTRIM(CONVERT(varchar(20),'' + COLUMN_NAME + '',120)),''''µ'''')'' WHEN ''date'' THEN ''ISNULL(RTRIM(CONVERT(varchar(20),'' + COLUMN_NAME + '',120)),''''µ'''')'' 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(RTRIM('' + COLUMN_NAME + ''),''''µ'''')'' END + ''+''+''''''§''''''+''+'' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '''+@p_schema_name+''' and TABLE_NAME = '''+@p_table_name+''' AND COLUMN_NAME NOT IN ('+@p_exception_column_list+') AND COLUMN_NAME NOT IN ('+@p_key_column_list+')'; --print @sqlStatement; set @sqlStatement=cast(@sqlStatement as nvarchar(max)); execute sp_executesql @sqlStatement , N'@sqlString varchar(max) OUTPUT', @sqlString=@sqlStringtmp output; --print @sqlStringtmp set @sqlStringOut =LEFT(ISNULL(@sqlStringtmp, ''),LEN(@sqlStringtmp)-1) --RETURN LEFT(ISNULL(@sqlString, ''),LEN(@sqlString)-1) END
One usage could be like that :
USE [TPCH] GO DECLARE @RC int DECLARE @p_table_name varchar(100) DECLARE @p_schema_name varchar(20) DECLARE @p_exception_column_list nvarchar(2000) DECLARE @p_key_column_list nvarchar(2000) DECLARE @p_ColList4Hash nvarchar(max) DECLARE @sqlQuery nvarchar(max) SET @p_table_name='H_Lineitem' SET @p_schema_name='dbo' SET @p_exception_column_list ='''l_partkey''' SET @p_key_column_list='''l_orderkey'',''l_linenumber''' -- TODO: Set parameter values here. EXECUTE @RC = [dbo].[sp_get_hash_fields2] @p_table_name ,@p_schema_name ,@p_exception_column_list ,@p_key_column_list ,@sqlStringOut=@p_ColList4Hash OUTPUT ; set @sqlQuery= ' --DECLARE @pagenumber int = 1; --Page to return --DECLARE @pagesize int = 10; --number of lines to return per page DECLARE @total int; WITH CTE1 --Do your primary Query and filtering in the inner Common Table Expression AS ( SELECT '+ replace(@p_key_column_list,'''','') + ',' --+@p_ColList4Hash+' as ColList'+','+ +'HASHBYTES(''MD5'','+@p_ColList4Hash+') as HashBytesOnColList'+ ', ROW_NUMBER() OVER ( ORDER BY '+ replace(@p_key_column_list,'''','') + ' ASC ) AS line --This order by Determines your sort order , _LineCount = COUNT(*) OVER ( ) FROM '+@p_schema_name+'.'+@p_table_name+' WHERE [l_suppkey]=7162 ) SELECT TOP ( @pagesize ) -- Diplay results in outer Query '+ replace(@p_key_column_list,'''','') + ', --ColList, HashBytesOnColList'+ ', _LineCount , ( _LineCount / @pagesize ) _PgCount FROM CTE1 WHERE line > ( @pagenumber - 1 ) * @pagesize ORDER BY line --Must have an order by statement to make TOP Deterministic' --PRINT @sqlQuery EXECUTE sp_executesql @sqlQuery, N'@pagenumber int, @pagesize int', @pagenumber=1,@pagesize=100
The query @sqlQuery generated (Nota that Order_Key, line_number, and part_key are not in the concatenation of column passed to hashbytes) :
DECLARE @pagenumber int = 1; --Page to return DECLARE @pagesize int = 10; --number of lines to return per page DECLARE @total int; WITH CTE1 --Do your primary Query and filtering in the inner Common Table Expression AS ( SELECT l_orderkey,l_linenumber,HASHBYTES('MD5',ISNULL(RTRIM(CONVERT(varchar(10),l_suppkey)),'µ')+'§'+ISNULL(RTRIM(l_quantity),'µ')+'§'+ISNULL(RTRIM(l_extendedprice),'µ')+'§'+ISNULL(RTRIM(l_discount),'µ')+'§'+ISNULL(RTRIM(l_tax),'µ')+'§'+ISNULL(RTRIM(l_returnflag),'µ')+'§'+ISNULL(RTRIM(l_linestatus),'µ')+'§'+ISNULL(RTRIM(CONVERT(varchar(20),l_shipdate,120)),'µ')+'§'+ISNULL(RTRIM(CONVERT(varchar(20),l_commitdate,120)),'µ')+'§'+ISNULL(RTRIM(CONVERT(varchar(20),l_receiptdate,120)),'µ')+'§'+ISNULL(RTRIM(l_shipinstruct),'µ')+'§'+ISNULL(RTRIM(l_shipmode),'µ')+'§'+ISNULL(RTRIM(l_comment),'µ')+'§') as HashBytesOnColList, ROW_NUMBER() OVER ( ORDER BY l_orderkey,l_linenumber ASC ) AS line --This order by Determines your sort order , _LineCount = COUNT(*) OVER ( ) FROM dbo.H_Lineitem WHERE [l_suppkey]=7162 -- example ) SELECT TOP ( @pagesize ) -- Diplay results in outer Query l_orderkey,l_linenumber, --ColList, HashBytesOnColList, _LineCount , ( _LineCount / @pagesize ) _PgCount FROM CTE1 WHERE line > ( @pagenumber - 1 ) * @pagesize ORDER BY line --Must have an order by statement to make TOP Deterministic
and return that
l_orderkey l_linenumber HashBytesOnColList _LineCount _PgCount
----------- ------------ ------------------------------------ ----------- -----------
19235 2 0x9586390E1875427A0C28D1E048D5A15B 588 58
74663 1 0x87B526DB1FB7C96D51BB171F849C92C5 588 58
117186 1 0x8818811EE5FEC891BEDE09F291553D31 588 58
126629 3 0xAEBBA057C24D34156EA369BBA86D735E 588 58
154119 6 0x04A21EAF53942A312728038859D75AB2 588 58
154499 1 0x16B5F91BA20EF351F937E56D90F8DAD5 588 58
196450 3 0xD9AC38CC5E23331F4875BDE130FE7097 588 58
196584 4 0x2FBB4BF330B209E7BD006D3F36A96580 588 58
271651 1 0xB1BC1E464ACCEFA0F94CA8FBC3F9F4B3 588 58
275400 1 0xC4E97D393527E961317933FA413352CC 588 58
(10 row(s) affected)
Hope that help !