Abhijit More (10/9/2013)
2. Audit - I have created verticle design having columns - Key ID, Table Name, Column Name, Old Value, New Value, Modified Date. Let's name the table as "AuditLog". In search also I have requirement to show audited information in verticle format. The user can search on field name also.
You've forgotten the all-important "Modified By" column.
My question would be, how are you going to populate this table? Before you answer, it should be done by trigger but, based on the fact that you were complaining about the number of tables for "like" audit tables, I'm hoping you haven't made the extremely severe mistake of using a "Generic CLR Audit Trigger" for this. We had those on our system (previous guard put them there) and it caused a huge bogging down of the system (I fixed it with some T-SQL, instead). Depending on the width of the source table, it would take up to 4 minutes just to audit a 4 column update on a lousy 10K rows because CLR triggers (usually caused by scope loss of the INSERTED and DELETED logical tables of the trigger).
Also and in case you haven't thought about it, you might want to consider using SQL_Variant for the "Old Value" and "New Value" columns. The reason is that SQL_Variant stores metadata about the data such as what the type of data stored is.
Last but not least, consider NOT logging any data for INSERTs and DELETEs other than just marking the event with a single row in the audit table. Logging all values for all columns for such things can easily cause your database to grow by a factor of 2 to 14 depending on the data in the tables.
--Jeff Moden
Change is inevitable... Change for the better is not.