Using table auditing in order to have "snapshots" of table

Question

I need a solution for the following problem:

I will have a table in SQL server 2008 that will be changing daily. It's a small table with maybe 5000 rows at most and around 5 fields.

The client will need the ability to view all the data in the table the way it was at a given point in time. For example "show me what all the data looked like in the table 2 weeks ago".

Someone had mentioned that auditing the table with http://autoaudit.codeplex.com/ would solve this problem.

My questions:

1. Is there a solution to this problem that would involve simple auditing?
2. If so, then how would i use auditing to solve this problem?
3. Is there a different solution already in existence for this sort of challenge so that I do not have to reinvent the wheel?

Answer 1

Have an auditing table that get's manipulated by triggers. Something like:

create table YourAuditTable
(
    -- all of your source table's columns
    CreateDate datetime not null,
    DeleteDate datetime null
)
go

And your trigger would look like this:

create trigger AuditYourTable
on dbo.YourTable
after insert, update, delete
as

    if exists(select * from inserted)
    begin
        if exists(select * from deleted)
        begin
            -- this is for an update
            update YourAuditTable
            set DeleteDate = getdate()
            where YourIDCol in (select YourIDCol from deleted)
        end 

        -- this is just for an insert
        insert into YourAuditTable
        select *, getdate() as CreateDate
        from inserted
    end
    else
    begin
        -- this is just for a delete
        update YourAuditTable
        set DeleteDate = getdate()
        where YourIDCol in (select YourIDCol from deleted)
    end

go

What this will allow you to do is query your audit table for a point in time. In other words, just use DATEDIFF to determine if a row was created prior to that given point in time, and deleted after it (or not deleted at all).

EDIT

To query your audit table for point in time data:

select *
from YourAuditTable
where CreateDate <= @PointInTimeDateTime
and
(
    DeleteDate is null or
    DeleteDate > @PointInTimeDateTime
)

Answer 2

You can also store history directly in your table. You add an activedate and an inactivedate column. If you don't care about the time of the insert or change, then use a date column instead of datetime.

If this is done on an existing sytem, tou change the name of the table. You write a view that shows only those records currently active and name it what the old table used to be called (so all the old code doesn't break).

If this is a new system, create the table with the columns mentioned above and still write a view that gets only the active records. This will enable your developers to consistently use the active view so they don't forget to filter the records when they want to show the data as it stands right now.

You will have to change how you would normally do your update record process (I'd do this in an instead of trigger) so that when a record is updated, it puts in the inactive date in the current record and adds a new one. Do the same with a delete using an instead of trigger to inactivate the record rather than deleting it. If this is anew process, you could skip teh triggers and simply write updates that do the same thing and updates to the inactive date filed when you want to delte. However, I find the triggers are morerelaible in terms of ensuring data integrity. I would personally do the instead of triggers and tehn let developers write normal updates and deletes in the user interface. This would ensure that all chagnes are properly handled whether they came from the GUI or from an adhoc update in SSMS.

Then you write a stored proc with a dateparameter to return the data active on the date input (you may need some special code to handle if a date input was earlier that the data you started doing this), then use this in the GUI form for the user to see the data as of a certain date.

Answer 3

This is for MS SQL. (Will also only work if you need to show data for a spesic date, if you need to drill down to a more precise point in time go with the audit table answer.)

Seeing as the table is so small, you are best of to use the Snapshot functionality provided by MS SQL.

To make a snapshot of a database:

CREATE DATABASE YourDB_Snapshot_DateStamp ON
( NAME = YourDB_Data, FILENAME = 
'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\YourDB_Snapshot_DateStamp.ss' )
AS SNAPSHOT OF YourDB;
GO

See this page for reference: http://msdn.microsoft.com/en-us/library/ms175876.aspx

You can make as many snapshots as you want. So my advice is to create a script or task that creates a daily snapshot and appends the date to the snapshot name. This way you will have all your snapshots visible on your server.

Important to note: Snapshots are read only.

Answer 4

You could implement your tables as "temporal tables".

What happens here is that the only "real" table is the table holding the history. But you also have an indexed view that you can treat, for most intents, in exactly the same way as your original table. And since it's an indexed view, you can add any additional indexes that you need for performance purposes.

There is an overhead on Insert/Update/Delete performance (but there will be for any trigger-based solution).

It does easily support the "point in time" access that you're asking for (via a UDF).

If there's anything you'd like adding or explaining with this, please let me know - I wrote the article (and a follow up) quite a few months back, but then ran out of steam/ideas on what to add next.