SQL SERVER bitmask计算 以及溢出问题

SQL SERVER 中计算bitmask通常是这么算的

Check whether columns 2, 3 or 4 has been updated.
If any or all of columns 2, 3 or 4 have been changed, create an audit record.
The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14.
To check if all columns 2, 3, and 4 are updated, use = 14 in place of >0 (below).
（摘自MSDN文档）

不过如果表中的列比较多(超过31列)，就有溢出的危险
可以改以下数据类型

select @preferBitmask=sum(power(@two, (colid-1))) from syscolumns where id = object_id(@targetTable) and objectproperty(id,'IsUserTable')=1 and name in (select Value from dbo.StrSplit(@columns, ','))

写成一个函数

CREATE FUNCTION [dbo].[ComputeBitmask]
(
    -- Add the parameters for the function here
    @targetTable varchar(128), @columns varchar(2000)
)
RETURNS int
AS
BEGIN
    -- Declare the return variable here
    DECLARE @preferBitmask int, @two float
    set @preferBitmask = -1
    set @two = 2.0000
    select @preferBitmask=sum(power(@two, (colid-1))) from syscolumns where id = object_id(@targetTable) and objectproperty(id,'IsUserTable')=1 and name in (select Value from dbo.StrSplit(@columns, ','))
 
    -- Return the result of the bitmask
    RETURN @preferBitmask

END
其中
StrSplit是一个自定义函数，用以解析@columns(用,隔开的列名)