Sql-server – HashBytes Computed Column: Exclude Column

computed-columndatabase-designhashingsql serversql-server-2016

We want to create Hashbytes computed column Non-Persisted. Table has twenty columns, and would like to exclude IdentityId column. How could I do this in computed column? Is there a SQL library function to exclude certain column? Purpose is for easy maintenance and as columns get added, don't want developers to forget adding extra columns. So hashbyte should always take all columns, exclude identity.

This is manual way typing all the columns:

CREATE TABLE [dbo].[CustomerTransactiont]
(
    CustomerTransactionId int primary key identity(1,1),
    CustomerName varchar(255),
    Price decimal(10,2),
    Quantity int,...
    .....
    RowHash as hashbytes('SHA2_512', CONCAT(CustomerName,'|'
                                                ,Price), '|'
                                                ,Quantity), '|'...
                                                )) 
)

Reference:
How to create a Hash Computed Column for Many Columns?

Best Answer

You could script this as part of your release process. EG something like:

declare @script nvarchar(max)
with q as
(
    select   concat(N'
    alter table ',quotename(schema_name(t.schema_id)),'.',quotename(t.name),' drop column if exists RowHash;
    alter table ',quotename(schema_name(t.schema_id)),'.',quotename(t.name),'
    add RowHash as hashbytes(''SHA2_512'', concat('''',',STRING_AGG(cast(quotename(c.name) as nvarchar(max)), ',''|'',') WITHIN GROUP (ORDER BY c.column_id ASC),'));
    ' ) cmd
    from sys.tables t
    join sys.columns c
      on t.object_id = c.object_id
    join sys.types ty 
      on c.user_type_id = ty.user_type_id
    where c.is_identity = 0
     and t.object_id in (select object_id from sys.columns where name = 'RowHash') 
     and c.name <> 'RowHash'
     and t.is_ms_shipped = 0
     and c.is_computed = 0
     and ty.name not in ('hierarchyid','xml', 'geography', 'geometry')
    group by t.object_id, t.schema_id, t.name
)
select @script = string_agg(cast(cmd as nvarchar(max)) , N'
') 
from q

print @script
--set xact_abort on
--begin transaction
--exec sp_executesql @script 
--commit
Related Question