Some suggested minor tweaks and some simple feedback

Jan 14, 2016 at 3:47 AM
Edited Jan 14, 2016 at 3:52 AM
Didn't take into consideration char data types. I changed line 102 in the procedure [sp_StandardCDC_enable_table]
from
WHEN TQ.dataTypeName = 'varchar' AND TQ.max_length > 0 THEN TQ.dataTypeName + '(' + CONVERT(varchar(10), TQ.max_length) + ')'
to
WHEN TQ.dataTypeName IN ('varchar', 'char') AND TQ.max_length > 0 THEN TQ.dataTypeName + '(' + CONVERT(varchar(10), TQ.max_length) + ')'
It didn't work well with tables that had multiple primary key columns. I changed line 128 in the procedure [sp_StandardCDC_enable_table]
from
 LEFT JOIN sys.indexes AS i ON i.[object_id] = c.[object_id] 
to
 LEFT JOIN sys.indexes AS i ON i.[object_id] = c.[object_id] AND i.is_primary_key = 1
There was an error when using the flag @CaptureUser = 1. I had to do a find and replace for AuditName to AuditUser in the procedure [sp_StandardCDC_enable_table].

I didn't like that it didn't know exactly what order the changes occurred in, so I added to the following at line 162 again in the procedure [sp_StandardCDC_enable_table] to give me a way to order it.
+ 'AuditVersion int IDENTITY(1,1)' + CHAR(10)
Some records can be inserted and updated etc all at the same time (single transaction), adding the identity gives me a way to know what order within the transaction it occurred also;

The rest of the logic so far seems fine. Sorry if the line numbers above are out by one or two lines, I wrote this up after I fiddled a bit.

Saw a lot of people asking how it works, don't know why it's only 6 scripts.
It creates a set of tables in a separate schema (scdc) to track whats on and off, just an easy way to figure it out. When enabled for a table it creates an AFTER INSERT/UPDATE/DELETE trigger on the table which writes out to a mirror of the table with a couple of extra fields. It's neat in that it allows you to specify what filegroup the scdc tables get created. So they can be separated from the main data files which also means you can push them to another drive if you wanted too.

This is a reasonable solution, it does put a minor load on the server since it's causing extra writes out to another table but I think the difference between how much load this and Change Tracking does is minimal, the triggers are about as simple and elegant as they can be, and reasonably formatted. CDC itself uses the log file to determine changes so it is way better performance wise, but if you're not running Enterprise this is a good alternative.

Final note, our product provider didn't want us putting triggers on their database but they were happy for us to run transactional replication. So I put my triggers on the replicated database tables. Unfortunately if I make changes to the replication schema or have to do some type of re-initialisation or recovery I have to manually figure out what the triggers may have been missed and re-implement the triggers after recovery.