How to use HASHBYTES function in sql server for multiple columns

• TRACEY-320982

  SSChampion

  Points: 13653

  More actions

  April 9, 2021 at 3:12 am

  #3867071

  I played with this and i need to store the hashbytes value in the Target database that only has a column of int that i can use.
  I also had to run the update twice for changing the address to 725 before the hashbytes and rowversion changed.  Any ideas why?
  
  Also if i store as int value in my target table - i just stored this rowversion in this table to try this out.
  
  How do i go from Rowversion value (which is the hashbytes value as int ) back to the binary value e hashbytes value.
  
  select * from targetdatabase.dbo.targettable a 
              where a.rowversion (help here to convert to hashbytes) = hashvalue
  
  
  
  
  
  Thanks
  
  
  create table dbo.hashbytes_demo
  (
  id char(32) not null,
  name varchar(25),
  address varchar(250),
  Systemmodstamp datetime2,
  HashValue as Hashbytes('SHA2_512', CONCAT('|', name, address,Systemmodstamp)),
  rowversion int,
  ---on sql 2016 and HashValue as Hashbytes('SHA2_512', CONCAT_WS('|', name, address,Systemmodstamp)),
  
  CONSTRAINT PK_hashbytes_demo PRIMARY KEY (Id)
  )
  
  insert hashbytes_demo( id , name , address)
  values-- (1, 'bob', '123 A street'), 
  -- (2, 'mark', '564 A street'),
  -- (3, 'ricky', '75 A street'),
  (4, 'ricky7', '5 A street'),
  (5, 'ricky7', '5 A street')
  ;
  select * from dbo.hashbytes_demo
  
  ---Test 1
  update dbo.hashbytes_demo
  set address = '72 A street' ,
  rowversion = HashValue
  where id =4;
  
  
  update dbo.hashbytes_demo
  set address = '72 A street' ,
  rowversion = HashValue
  where id =5;
  
  --Both shoudl be the same
  select * from hashbytes_demo
  
  --update one
  
  update dbo.hashbytes_demo
  set address = '725 A street' ,
  rowversion = HashValue
  where id =5;
  
  ---This doesn't set the rowversion it remains as -1088069236 but the address changed
  select * from hashbytes_demo
  
  --if i run it again it works - it goes to -480135779
  
  update dbo.hashbytes_demo
  set address = '725 A street' ,
  rowversion = HashValue
  where id =5;