Category: SqlServer

Maintaining history of data


What is this about?

I tend to quote Fowler: “Usually when we see properties on a class, they represent questions we can ask of an object now. However there are times when we don’t want to just ask questions about a property of an object now, we also want to ask these questions about some point in the past when things may have changed. “

For example , we want to track every change that occurs at an Employee ( changing name, salary) and see the previous versions( what was the salary 2 years ago ?)

So what are the software solutions to do an audit log ? First, we should have a history table for each table that we want to maintain an audit log. E.g. Employee => EmployeeHstory

Solution 1 : Coding / Event Sourcing

That means code should maintain history himself – i.e. adding to the history table every modifications.

For Entity Framework this can be done easy, by overiding SaveChanges.

Demo at 

Solution 2 : Table Triggers

This can be done from database . There are triggers for insert , update, delete – and those triggers insert the data in the history table

Demo at

Solution 3, Temporal Tables, Microsot  Sql Server 2016 :

Documentation at 

Limitations at 

Demo at

Monitor SqlServer Stored Proc execution time

I have had the task to made something to monitor what stored procedure, in Sql Server,in most used. The problem that I have had is that the host_name is easily changed by putting, in the connection string WSID (see )
The code is for sql server 2005 and I have not realized without the help of
So there are the three easy steps to have the monitor proceudre in place:
1. Define an audit table :

	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ProcName] [varchar](128)
	[IdentifierAccess] [varchar](max)
	[DateAccess] [datetime] NULL DEFAULT (getdate())

2. Stored proc to insert data :

alter proc dbo.proc_Log(@procname varchar(100))

declare @userid varchar(100)
select @userid=client_net_address from sys.[dm_exec_connections] with(nolock)
where session_id = @@SPID
--GRANT SELECT ON sys.dm_exec_connections TO the user or
--grant view server state to the user !
set @userid = SUSER_NAME()


3. In each stored procedure put this at beginning

DECLARE @ProcName nvarchar(128)


    exec proc_Log  @ProcName

And this at the end of stored proc :

    exec proc_Log  @ProcName

4. See what the procedure calling are and the time (basic)

select a1.*,'--',a2.* , datediff(ss,a2.DateAccess,a1.DateAccess) 
from [Audit] a1  inner join [Audit] a2 on
a2.ID =
( select max(ID) from [Audit] a2 where a1.ProcName= a2.ProcName and a1.IdentifierAcces=a2.IdentifierAcces and a1.ID > a2.ID )
order by 1

From there is a simple way to make a group by to have the sum * count or other relevant data ( which proceudres are most used in what times of the day, and so on)

Work from home :
1. From the previous select(put it into a Common table Expression ), see what procedure use most
a) number of uses
b) number of time
2. What is missing from Audit table definition ? (Hint :easy clustered index definition)

Sql Server Management Studio free awesome addons

The two addons that I can not live without in Sql Server and that, more , are free :

  1. SSMS Tools Pack – now ,
  2. SQL Search – now1.0


The SSMS Tools Pack maintains a history of your commands . More, it saves on the C:\SSMSTools\SQLQueryActionLog



The Sql Search finds a text in the objects in the database – very useful if you decide to change a column name and find all stored procedures that have this reference