Suggestions for implementing audit tables in SQL Server?

How much writing vs. reading of this table(s) do you expect?

I’ve used a single audit table, with columns for Table, Column, OldValue, NewValue, User, and ChangeDateTime – generic enough to work with any other changes in the DB, and while a LOT of data got written to that table, reports on that data were sparse enough that they could be run at low-use periods of the day.

If the amount of data vs. reporting is a concern, the audit table could be replicated to a read-only database server, allowing you to run reports whenever necessary without bogging down the master server from doing their work.

Leave a Comment