• I have always used audit tables and triggers for this type of auditing. Yes, you could cause a problem if the application code is using @@Identity to return the latest identity value if you put an identity column in your audit table. If you don't know what was used I would suggest not using an identity value on the audit table. My audit table would add these columns to the columns in that source table:

    audit_action char(1) -- values would be I, U, D for insert, update, delete

    audit_date smalldatetime

    Then your unique/primary key would be source_id, audit_date, audit_action. Odds are the only joining you would do on the table will be on the source id you'd use that index for any join operations. You could cluster on audit_date, but I would think that a heap would be okay in this instance.