Tracking Changed Data In Standard Edition

Mickey Stuewe wants to track changed data, but has to use Standard Edition:

I use a pattern that includes four fields on all transactional tables. This (absolutely) includes lookup tables too. The two table types that are an exception to this pattern are audit tables and error tables. I’ll cover why later in this article.

Four fields include CreatedOn, CreatedBy, UpdatedOn, and UpdatedBy. The dates should be DateTime2. CreatedOn is the easiest to populate. You can create a default on the field to be populated with GetDate().

This is a common pattern and works pretty well.  The trick is making sure that you keep that metadata up to date.

Related Posts

Database Migrations With DBATools

Jason Squires has a series on database migrations using dbatools.  The first part covers capacity planning: Great results, I now know that I cannot move the database from one server to another on any of the drives shown without expanding the drive volume.  Even though this was an awesome output, I wanted more. I know […]

Read More

Myths With Page Life Expectancy

Kevin Hill has a public service announcement: I’ve been hearing throughout my entire DBA career that 300 seconds is a good counter for PLE (Page Life Expectancy) to be above. Paul Randal calls this “utter nonsense“.  If anyone would have the right to say that, its Paul. It is good for the number to be […]

Read More

Categories

January 2016
MTWTFSS
« Dec Feb »
 123
45678910
11121314151617
18192021222324
25262728293031