Improving Audit-Field Tracking

Most SQL Server shops audit information such as who made the last change to a record and when. This information can help you figure out how many transactions each person on your data-entry team enters each day. You might also use such information to help safeguard confidential data.

To track this kind of information, our organization adds fields such as au_user_name and au_last_update_date to our database tables. We typically use field layouts like au_user_name nvarchar(50) and au_last_update_date datetime.

For example, Figure 2 shows the Northwind database's Customers, Orders, and Order Details tables with the addition of the columns au_user_name and au_update_date in each table. When we want to write applications that use data from this database, we use the script that Listing 2 shows, which includes the au_user_name and au_update_date columns in its INSERT and UPDATE statements. This approach provides details about who made a change and when, but it creates redundancy in the database and occupies a lot of space. For example, if you have 10 people on your data-entry team, the maximum number of distinct values in the au_user_name column would be 10. If you execute the statement

SELECT DISTINCT au_user_name FROM Customers

SQL Server might return only 10 rows. But if you perform the same SELECT on a table that has a million rows, you've added a huge amount of data to the process. By making a small change in the way we process this information, we can return the same tracking information and eliminate the redundant processing.

To modify the tracking method described above, add the AUDIT table that Figure 3 shows to your Northwind database. In the AUDIT table, audit_id is defined as IDENTITY with increments of 1. Then, in the Customers table, replace au_user_name with au_user_id, which is a smallint data type.

Joining the AUDIT and Customers tables returns the same level of detail and reduces the amount of disk space required to hold the tracking information. Listing 3 and Listing 4 show the code we used to change Listing 2's stored procedures for inserts and updates on the Northwind Customers table. To retrieve information about who made each change and when, you would use the following SELECT statement:

SELECT CustomerID, CompanyName, ContactName,ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax, audit_useras au_user_name, au_update_dateFROM Customers INNER JOIN AUDITON AUDIT.audit_id=Customers.au_user_id

These changes let you track the same detailed information as the old approach, but depending on the number of database users you have, you'll save quite a bit of disk space.

—Raj Rathi
[email protected]