Click here to Skip to main content
15,566,522 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

What I have tried:

alter table [dbo].[fmdchemical] add hashchemical nvarchar(700) null
 update ch  set ch.hashchemical =HASHBYTES('SHA2_512',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

 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')
Updated 17-May-22 7:09am

1 solution

The length of a hash does not depend on the length of the input to that hash. For SHA256, the output will always be 32 bytes. For SHA512, the output will always be 64 bytes.

You should declare your column as varbinary, not nvarchar. Storing the signature in a string column will require more storage space, depending on the format you decide to use. For example, the Base64 representation of a SHA256 signature would require 44 characters, and with an nvarchar each character would require two bytes, giving a total of 88 bytes to store a 32-byte value.
Share this answer
ahmed_sa 17-May-22 12:14pm    
this is excellent answer for my question
so 1- which is best SHA256, or SHA2_512
2-compare multi column hash with each other is best
or concatestring string hash on one column and compare it
3-can i create index on column varbinary
Richard Deeming 17-May-22 12:17pm    
So long as you don't use varbinary(max), you can create an index on the column.

Your other questions are unanswerable. You'll need to do some research and testing against your own data and queries to find out what the "best" options are.
ahmed_sa 17-May-22 12:20pm    
so which is best length i can use for my case above
i use varbinary(32)
Msg 8152, Level 16, State 30, Line 2
String or binary data would be truncated.
The statement has been terminated.
Richard Deeming 17-May-22 12:23pm    
If you're using SHA2_512, the output will be 64 bytes; use varbinary(64).
ahmed_sa 17-May-22 12:31pm    
are will bad performance if i make it varbinary(300) or no need

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900