Post
Ask Question
ahmed salah

ahmed salah

  • 1.2k
  • 487
  • 30.2k

which is best use hashbyte sha2_256 or sha2_512 for performance ?

May 17 2022 4:28 PM

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')

also which length is best for store hash varbinary(???????) what 

which length 

and what issue can i face on large size 


Answers (1)