Auditing data changes in SQL Server I am tasked with implementing auditing of data changes on our database.  I am aware that there are commercial products for this, but I wanted to know which design would be better.  For any given row we will want to know what values were changed, who changed it, and when the change took place.

We have Enterprise Level data (possibly millions of rows), and we are using MS SQL Server Web (ver.13).

A suggested design used histid as the unique primary key on every table; What would normally be the unique primary key would be repeated (once for each version of the row):

-- primary key is histid.
-- the current programAssignmentId is what I am  typically searching for.

SELECT 
    [histid] -- column is primary key.
    ,[isCurrent]
    ,[lastupdate] -- column is indexed.
    ,[programAssignmentId] -- column is indexed.
    ,[programid] -- foreign key, column is indexed.
    ,[entityid]  -- foreign key, column is indexed.
    ,[lastUpdatedById] -- foreign key, column is indexed.
    ,[effectiveDate]
    ,[orderno]
    ,[status]
    ,[deleted]
    ,[created]
FROM [dbo].[mem_programAssignments]
WHERE [programAssignmentId] = 'a43c2a3e-d3a7-4f40-aeb0-cc0552c62da2'
ORDER BY [programAssignmentId], [lastupdate] DESC


Would yield the following results:

histid                                isCurrent   lastupdate                programAssignmentId                    programid                           
===================================   =========   =======================   ====================================   ====================================
7330075F-B076-113D-B548CDDD0BF5D4B8   1           2017-08-17 13:37:56.950   a43c2a3e-d3a7-4f40-aeb0-cc0552c62da2   5f4d1469-44a0-49c7-856d-f35bf729e661
73291EC6-F168-E817-EB42EB46A5AFB08C   0           2017-08-17 13:37:11.670   a43c2a3e-d3a7-4f40-aeb0-cc0552c62da2   5f4d1469-44a0-49c7-856d-f35bf729e661
56F73DE0-0137-4CBF-969AEDFE55229E3B   0           2017-04-24 18:49:11.000   a43c2a3e-d3a7-4f40-aeb0-cc0552c62da2   5f4d1469-44a0-49c7-856d-f35bf729e661


A competing design suggested one or more history tables that would record the changes.  

SELECT 
   [id]
    ,[reference_class_name]
    ,[reference_table]
    ,[reference_id]
    ,[column_name]
    ,[old_value]
    ,[new_value]
    ,[created_at]
    ,[user_id]
    ,[user_name]
    ,[message]
FROM [dbo].[history]


Would output something like:

id   reference_class_name         reference_table          reference_id                            column_name   old_value    new_value  
==   =========================    ======================   =====================================   ===========   ==========   ===========
24   Models\ProgramAssignement    mem_programAssignments   b40b2d80-99d3-11e8-a0ad-29cac47b46c7    startDate     2017-01-01   2018-07-11 
25   Models\ProgramAssignement    mem_programAssignments   b40b2d80-99d3-11e8-a0ad-29cac47b46c7    deductible    15000000     30000000  


The reference_id in this case would be referencing the id value of the primary key of the table listed in the reference_table column (mem_programAssignments in this case).

The advantage to the second design is it would separate the historical values from the current values, but the obvious drawback would be that the history table(s) could get extremely large.

Is one design better than the other -- or I should say, what are the advantages, disadvantages, or risks with these designs?  Are there other design patterns I should consider?
For a relatively immutable tracking of history, System Versioned Temporal Tables may be the way to go as you are using a recent enough edition of SQL Server (similar support is available in some other databases). They have some gotchas that mean they are not suitable for all circumstances but if they fit your model they'll make life easier by implementing chunks of it for you "for free" and allowing you to use some useful time-travelling syntax such as SELECT &lt;stuff&gt; FROM &lt;table&gt; AS OF &lt;date_time&gt; and BETWEEN &lt;date_time&gt; AND &lt;date_time&gt; instead of more manually querying the history.

For an audit you need extra though, as well as what happened when you need to track who made the change. One option would be to add a user identifier column to each table being tracked, which would be included in the history too. If you are tracking users that all have their own SQL Server login then you can populate that by trigger, reading the value from SYSTEM_USER/SUSER_NAME(), though in the more common case of tracking users of an application that always logs into SQL Server using the same application account you will need to implement the population that column in your application logic.


  Is one design better than the other


Your second suggestion is essentially an extended property bag (where each property is actually property+time) which makes me wary of it. It is potentially flexible, but has its own significant issues which, for instance, make the model inefficient to report from. Lookup "Entity Attribute Value model" (the more technical term that "property bag" is usually a synonym for), on DBA.SE and elsewhere, for detailed discussions as to the pros and cons of this. It is commonly considered to be an anti-pattern.

isCurrent

If you do roll your own history structure instead of using the built-in support (because the built-ins are unsuitable for some reason, or you need to implement something more cross-platform compatible, for instance) then I would recommend against keeping all the data in your core tables and instead keeping current data on its own and a separate history. This will make querying current data, likely the most common thing you application will do, less error prone. If you keep all versions1 in your history structure you may still have an isCurrent flag in those structures.

1 SQL Server's temporal tables don't keep the latest version of a row in the history table (unless it has been deleted, depending on if you conceptually consider the last existing version to be the latest or not) only copying the previous version in as it is modified. Some history/audit implementations do hold the latest version of each row as well as the past ones so that the history table contains everything.