Suggestions for implementing audit tables in SQL Server?

by

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.

Added:
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.

More Related Contents:

  1. Is it possible to use greater than equal to operator in group by? [closed]
  2. Solutions for INSERT OR UPDATE on SQL Server
  3. How can I schedule a job to run a SQL query daily?
  4. What’s the best practice for primary keys in tables? [closed]
  5. Reset identity seed after deleting records in SQL Server
  6. Querying data by joining two tables in two database on different servers
  7. Generate a resultset of incrementing dates in TSQL
  8. What is best tool to compare two SQL Server databases (schema and data)? [duplicate]
  9. How Can I use “Date” Datatype in sql server?
  10. How to replace a string in a SQL Server Table Column
  11. What is the difference between using a cross join and putting a comma between the two tables?
  12. Pivot Table and Concatenate Columns
  13. SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
  14. Correct use of transactions in SQL Server
  15. How to run a stored procedure in sql server every hour?
  16. Composite Primary key vs additional “ID” column?
  17. Why do you create a View in a database?
  18. Logging ALL Queries on a SQL Server 2008 Express Database?
  19. The backend version is not supported to design database diagrams or tables
  20. How to check which locks are held on a table
  21. Know relationships between all the tables of database in SQL Server
  22. How do I create and query linked database servers in SQL Server?
  23. The object ‘DF__*’ is dependent on column ‘*’ – Changing int to double
  24. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
  25. How do I change db schema to dbo
  26. The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions
  27. Functions vs Stored Procedures
  28. Space used by nulls in database
  29. Executing SQL query on multiple databases
  30. Single or multiple databases

Leave a Comment