Luke C (4/26/2011)
Solomon, this is a great post and thanks for sharing! Just this last week I took a similar approach with 4 large tables that had multiple columns being changed from varchar to nvarchar to support our localization effort. Much of the techniques you mention here were used with the exception of triggers. Given that we were on SQL Server 2008 I was able to use Change Tracking in place of triggers which resulted in 0 modifications to the existing tables. I'd be interested to hear how other folks have used Change Tracking or Change Data Capture.
Hey Luke. Thanks! CDC is an interesting option that I did not think of due to us being on SQL Server 2005. But if I get the chance (someday :-)), I will revise this article with that option.
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR