How big should I make a column in an audit table for storing the output of COLUMNS_UPDATED()?

Question

I have a stored procedure which creates a complementary trigger and audit table when a table name is passed into it. The SP filters out all the calculated columns and other stuff that stops you doing an 'SELECT *' for auditing and then writes a trigger that inserts those specific columns from table into table_audit upon DELETE/INSERT/UPDATE along with some audit data such as HOST_NAME() and COLUMNS_UPDATED(). This has been little used but has generally worked for the clients who've asked for it.

In a recent round of testing I was asked to set up the auditing on a test database. This caused inserts in our main table to fail because "string or binary data would be truncated", after investigation I found the column where the results of COLUMNS_UPDATED() was being stored was the issue.

The definition of the column was:

[UpdateColumns] [varbinary](16) NULL

Changing the definition to this has made everything work again:

[UpdateColumns] [varbinary](24) NULL

However what this highlights is that I don't understand the relationship between the number of columns in the table (95 in this case, 7 of which are calculated) and the size of the output from COLUMNS_UPDATED(). I thought [varbinary](16) = 128bits, which should be more than enough flags for 95 columns.

So my question is: what is the relationship?

Secondary question: Can I easily derive from the number of columns that will be audited a value for x in [UpdateColumns] [varbinary](x) when building the trigger or would I be better off just setting x to some larger number?

Answer 1

Let me start out with a question: Why do you store the value of COLUMNS_UPDATED() if you don't understand how to read it? What possible value can you get out of storing it?

But to answer your question, COLUMNS_UPDATED() returns one bit per column that is currently in the table or was previously in the table. COLUMNS_UPDATED() is based on the column_id that you can retrieve from sys.columns. Every new column get an increasing value assigned in there, kind of like an IDENTITY value, however restarted for each table. So if you remove and re-add columns old values will not get reused; instead you will have gaps. The following code snippet demonstrates that:

CREATE TABLE dbo.tx(c1 INT, c2 INT, c3 INT);
ALTER TABLE dbo.tx DROP COLUMN c2;
ALTER TABLE dbo.tx ADD c4 INT;

SELECT name, column_id FROM sys.columns WHERE object_id = OBJECT_ID('dbo.tx');

This is the output of this query:

|name | column_id|
+-----+----------+
|c1   | 1        |
|c3   | 3        |
|c4   | 4        |

That has the big advantage that you do not have to change trigger code that was looking for a specific column. However, it has the huge disadvantage that that everything gets obscured. Also you might not be able to script out your tables and triggers and recreate them in a testing database because there the column_id values might be different.

So to figure out how many bits you need, just look at the MAX(column_id) value for your table.

An alternative is to use the UPDATE() function that takes a single column name and returns a value indicating if that column was changed or not. Both functions have the additional disadvantage that they do not actually compare the values; they just report that the column was referenced, even if the value in the column stayed unchanged.

In your case it looks like you are trying to capture the actual data changes. Have a look at Change Data Capture - no need to reinvent the wheel.