Eric M Russell (10/7/2015)
mister.magoo (10/6/2015)
I don't know whether your setup would suit this, but how about converting to xml , then using hashbytes?
select MBT.refID,hashbytes('MD5',(select MBT.* from (values(null))foo(bar) for xml auto)) as [Hash]
from MyBaseTable as MBT;
You need the "from (values(null))foo(bar)" clause to use xml auto, it serves no other purpose.
Bravo, Mr. Magoo!
Leveraging XQuery to serialize all the columns in each row is brilliant. When I test this against a large table, the execution plan shows that 90% of the cost is the table scan and the UDX operator (the part that serializes the XML) is only 2% of the cost. So it appears we can compute an MD5 or SHA hash dynamically across all columns in a row.
Yes, I was surprised how well it worked, and just think of all the things we couldn't do without XML support in SQL.
MM
select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);