HashBytes 計算列：排除列

我們要創建 Hashbytes 計算列 Non-Persisted。表有二十列，並且想排除 IdentityId 列。我怎麼能在計算列中做到這一點？是否有排除某些列的 SQL 庫函式？目的是為了便於維護，並且隨著列的增加，不希望開發人員忘記添加額外的列。所以 hashbyte 應該總是取所有列，排除身份。

這是手動輸入所有列的方式：

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

參考： 如何為多列創建雜湊計算列？

您可以將此腳本作為發布過程的一部分。例如：

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

引用自：https://dba.stackexchange.com/questions/231015