can i store hashbyte on field with length smaller than fields i concatenate it?

ahmed salah 3,216 Reputation points
2022-05-17T15:20:39.23+00:00

I work on sql server 2019 i have table have multi column string concatenation as StrSubstance,strmass ,strcasnumber

i need to create filed hashchemical with nvarchar(700) and this will store hash for 3 columns concatenation with each other

are this possible

what i mean

alter table [dbo].[fmdchemical] add hashchemical nvarchar(700) null

update ch  set ch.hashchemical =HASHBYTES('SHA2_512',concate(StrSubstance,strmass,strcasnumber)) from [dbo].[fmdchemical] ch

so are hashing will be correct with nvarchar(700) and every field have length 3500

are this will make issue on the feature with big counts

also are hash every column alone then compare it or concate all these columns on one column then compare

CREATE TABLE [dbo].[fmdchemical](
     [id] [int] IDENTITY(1,1) NOT NULL,
     [chemicalid] [int] NULL,
     [StrSubstance] [nvarchar](3500) NULL,
     [strmass] [nvarchar](3500) NULL,
     [strcasnumber] [nvarchar](3500) NULL
 ) ON [PRIMARY]

 GO
 SET IDENTITY_INSERT [dbo].[fmdchemical] ON 

 INSERT [dbo].[fmdchemical] ([id], [chemicalid], [StrSubstance], [strmass], [strcasnumber]) VALUES (826807, 748787, N'3P04,AL2O3,C,C,Cr203,Cu,Cu,Fe,Mn,Others,others,others,Others,Pb,Si02,SiC,Silicone Resin,SiO2,Sn,Sn,Ti02', N'0.0004,0.0096,0.336,0.3992,0.5904,0.764,0.7904,1.5408,1.6176,1.6808,1.6808,2.9344,5.2392,7.2,7.9608,19.2072,32.08,65.3584,148.3384,232.7992,269.472', N'592910,592960,592961,593043,593043,593259,593290,593306,593306,593495,593501,593501,593505,593505,621442,621442,621442,621442,622089,624020,650559')
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,708 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Naomi 7,361 Reputation points
    2022-05-17T15:44:38.227+00:00

    You can use varbinary(32) to store the hash value in a table of your concatenated columns if you're using SHA2_256 algorithm. What exactly is the purpose and what do you want to compare?

    Check https://learn.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15

    0 comments
  2. Tom Phillips 17,716 Reputation points
    2022-05-17T18:21:35.54+00:00

    The output of HASHBYTES is a varbinary of a size based on the algorithm used. The output size has no relationship to the input size.

    See:
    https://learn.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql?view=sql-server-ver15

    The output conforms to the algorithm standard: 128 bits (16 bytes) for MD2, MD4, and MD5; 160 bits (20 bytes) for SHA and SHA1; 256 bits (32 bytes) for SHA2_256, and 512 bits (64 bytes) for SHA2_512.

  3. Bert Zhou-msft 3,421 Reputation points
    2022-05-18T02:27:54.863+00:00

    Hi，@ahmed salah

    Welcome to Microsoft T-SQL Q&A Forum!

    It's not very clear why you want to use Varchar , HashBytes can only accept up to 8000 characters , obviously varbinary(64) is better . If you want to create a hash of a string larger than the limit, you will have to resort to the CLR or chunk the string .
    I have done this in T-SQL using :

    1. Cut the string into 8000/4000 characters；
    2. Hash each block；
    3. Concatenate the hashes and test the length；
    4. Hash join, repeat chop if limit is exceeded；
    5. End with a hash at the end.

    Best regards,
    Bert Zhou

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments