Audit approach for performance tunning

Question

Audit approach for performance tunning

Megha P

SSCommitted

Points: 1976
More actions
April 21, 2014 at 4:48 am

#288146

we do have requirement of tracking insert,update and delete action of tables with tracking userid.
two approach is there.
1) creating single table with below fields:
LogID int
TableName int
Action tinyint (1= Insert,2=Update,3=Delete)
RecordID int
UserID int
CreatedDate DateTime(Default will be current date)
2) Add below fields in all the tables:
modifieddate datetime
modifiedUser int
transactionType Char(1) --this will have action like 'I','D' or 'U' for insert update and delete
which approach will be good for performance ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

The second approach would require to replace the DELETE functionality with a solution that would add a DELETE flag. Otherwise you wouldn't have a row to add the modified* information 😉

Maybe this link[/url] can help you to decide what solution would be the best for your scenario.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

It's not about performance, it's about what meets your requirements.

Option 2 will only show the last user who changed the row and won't show what they changed. If you need to see all the users who modified the row, then option 2 won't suffice.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

azmiman SSC Rookie Points: 43 More actions · Answer 3

GilaMonster (4/21/2014)
It's not about performance, it's about what meets your requirements.
Option 2 will only show the last user who changed the row and won't show what they changed. If you need to see all the users who modified the row, then option 2 won't suffice.

Is there any online resource that I can read on how to audit/track who change what value in a record? My audit requirement:

if some one changed a value / values in a record, I need to know who edit the record and which attribute(s) value has been changed (if possible, i need to know what was the original value)..

Thanks!