tsql - How to do bitwise OR between two arbitrary long VARBINARY(MAX) values in Sql Server? -

motivation:

i have trigger when of text fields of table updated or inserted into.

the code must not hard code table knowledge. i.e. should work on arbitrary tables.

where did far

1. ddl trigger on create_table adds for insert,update dml trigger new table
2. a _columnmask table mapping 1-based integers respective varbinary(max) masks compatible columns_updated format.

having _columnmask table , aaaculture table, able write following query:

;with aux (     select (max(column_id) + 7) / 8 mask_len     sys.columns     object_id = object_id('aaaculture') ) select c.name, column_id, case when pad.zero null cm.mask else cm.mask + pad.zero end mask sys.columns c  join aux on 1 = 1 join sys.types t on t.user_type_id = c.user_type_id join _columnmask cm on cm.n = c.column_id left join _columnmask pad on pad.n = aux.mask_len - (c.column_id + 7) / 8 object_id = object_id('aaaculture') , (t.name '%char' or t.name '%text') order c.column_id 

yielding

name        column_id   mask text1       2           0x020000000000 text2       4           0x080000000000 text3       6           0x200000000000 text4       8           0x800000000000 text5       10          0x000200000000 text7       14          0x002000000000 text8       16          0x008000000000 text9       18          0x000002000000 text10      20          0x000008000000 text11      21          0x000010000000 text21      24          0x000080000000 text22      26          0x000000020000 text23      28          0x000000080000 text24      30          0x000000200000 text25      32          0x000000800000 text26      34          0x000000000200 text27      36          0x000000000800 text28      38          0x000000002000 text29      40          0x000000008000 xrefcode    42          0x000000000002 text12      44          0x000000000008 

for example, suppose update fields text9 , text23:

update dbo.aaaculture set text9 = 'haha', text23 = 'xoxo' 

the respective columns_updated value reported dml trigger is:

0x000002080000 

indeed, according aforementioned query result:

• text9 has mask of 0x000002000000
• text23 has mask of 0x000000080000

doing bitwise or between 2 masks yields 0x000002080000

hence seem have pieces of puzzle in order able recognize columns_updated() includes text columns. except, have no idea how efficiently (i write kind of ugly while loop, suppose).

any ideas?

edit 1

we update database adding db upgrade step sql script (under version control). goal prevent devs checking in db steps update or insert text fields in of xyzculture tables, including created new db steps. let not discuss, why have xyzculture tables in first place. there there, sigh, given.

i may resort preventing update field whatsoever in tables, first want understand how hard more specific.

helas, not come other following implementation:

create function varbinarybitwiseor(@x varbinary(max), @y varbinary(max)) returns varbinary(max) begin     declare @pos int = 0     declare @res varbinary(max)     declare @tmp varbinary(max)     while @pos + 8 <= datalength(@x)     begin         set @tmp = cast(cast(substring(@x,@pos + 1,8) bigint) | cast(substring(@y,@pos + 1,8) bigint) varbinary(max))         set @res = isnull(@res + @tmp, @tmp)         set @pos = @pos + 8     end     if @pos + 4 <= datalength(@x)     begin         set @tmp = cast(cast(substring(@x,@pos + 1,4) int) | cast(substring(@y,@pos + 1,4) int) varbinary(max))         set @res = isnull(@res + @tmp, @tmp)         set @pos = @pos + 4     end     if @pos + 2 <= datalength(@x)     begin         set @tmp = cast(cast(substring(@x,@pos + 1,2) smallint) | cast(substring(@y,@pos + 1,2) smallint) varbinary(max))         set @res = isnull(@res + @tmp, @tmp)         set @pos = @pos + 2     end     if @pos + 1 <= datalength(@x)     begin         set @tmp = cast(cast(substring(@x,@pos + 1,1) tinyint) | cast(substring(@y,@pos + 1,1) tinyint) varbinary(max))         set @res = isnull(@res + @tmp, @tmp)         set @pos = @pos + 1     end     return @res end 

it works when datalength of both arguments same, case exactly.

so, both slow , not general purpose (the datalength must same).