Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
1  
SQL Server 2005: no CDC. Sad trombone. –  Jon Seigel Aug 23 '13 at 17:15
    
"Why do you store the value of COLUMNS_UPDATED() if you don't understand how to read it" - because my requirements included "store the value of COLUMNS_UPDATED() in the audit" :) Is there a version of Change Data Capture which works in SQL Server 2005? All this stuff was originally implemented for SQL Server 2000... –  robertc Aug 23 '13 at 17:17
    
I am sorry, I missed the 2005 tag. No there is no build in solution in SQL Server 2005. However SQL 2005 is also out of mainstream support (for 18 month already: blogs.msdn.com/b/sqlreleaseservices/archive/2011/01/27/…), so you might want to urge the business to upgrade. –  Sebastian Meine Aug 23 '13 at 17:20
    
We only a few months ago managed to move from requiring SQL 2000 compatibility mode to requiring SQL 2005 compatibility mode, upgrade not likely to happen soon :( –  robertc Aug 23 '13 at 17:31
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.