HashBytes计算列:排除列

Question

我们想要创建非持久化计算的哈希字节列。表有20列，希望排除IdentityId列。我如何在计算列中做到这一点？是否有一个SQL库函数来排除某些列？目的是为了便于维护，并且随着列的添加，不要让开发人员忘记添加额外的列。因此，哈希字节应该始终接受所有列，排除标识。

这是手动输入所有列的方式：

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

参考资料：如何为许多列创建哈希计算列？

Answer 1

您可以将此脚本作为发布过程的一部分。例如：

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