Etoiles inactivesEtoiles inactivesEtoiles inactivesEtoiles inactivesEtoiles inactives
 

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 !


 

Ajouter vos commentaires

Poster un commentaire en tant qu'invité

0 / 30000 Restriction des caractères
Votre texte doit contenir entre 10 et 30000 caractères
Vos commentaires sont soumis à la modération de l'administrateur.
conditions d'utilisation.
  • Aucun commentaire trouvé