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

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.