Working Effectively with Legacy SQL

Click here to access demo code for this presentation.

The source code is licensed under the terms offered by the GPL.

Prerequisite Links: Source Control systems

These links are in no particular order. Which source control system you use matters less than simply using a source control system. My personal recommendation is Mercurial, although Git is a more popular choice.

• Git is an extremely popular distributed source control system, and tends to be the de facto choice for open source software.
• Mercurial is a competing distributed source control system and is used in some open source projects, although not to Git's extent.
• Subversion (SVN) is a venerable, centralized source control system. Although many companies are moving to distributed systems, you will still find SVN in a lot of workplaces.
• Team Foundation Version Control is the source control spin-off for Team Foundation Server. The TFS group offers support for TFVC as well as Git, but to me, the tea leaves indicate that they are moving away from TFVC over time. My recommendation is that you do the same.
• If you want a book on the topic, get a copy of SQL Server Source Control Basics, published by Red Gate.

Prerequisite Links: Database Testing

Having working, repeatable tests is vital for refactoring code. Although this presentation does not cover how to implement database testing in any detail, I want to provide a number of links to help people get started.

• Check out my two-part series on testing: the basics and the slightly less basics.
• tSQLt is a unit and integration testing framework for T-SQL. Red Gate has a tool named SQL Test which sits on top of tSQLt and provides it a pretty interface.
• Dave Green has a Pluralsight course on unit testing using tSQLt. It gets into a pretty good level of detail regarding the product, including faking dependencies.
• Dave Green also has a Simple-Talk article on tSQLt.
• Microsoft provides database test projects in Visual Studio.
• Red Gate has a nice book on team-based development which focuses a good deal on database testing.
• Alex Kuznetsov has a book entitled Defensive Database Programming, which also spends a good deal of time on database testing.
• Not all database testing has to be using formal tools. Robyn Page and Phil Factor's workbenches (such as their matrix workbench) are great examples of building a test harness "organically."

Basic Links

• All of this started with a blog series on legacy SQL.
• I recommend that you also read my series on design in an Agile world This explains some of the motivation behind my work here.
• The namesake for this presentation is a book by Michael Feathers, entitled Working Effectively with Legacy Code.
• Thoughts on when code becomes "legacy"
• Two non-SQL books I recommend on the topic are Clean Code and The Art of Readable Code. Both focus on object-oriented languages, but many of the tips apply to T-SQL as well.
• Another non-SQL resource is Cory House's Pluralsight course entitled Clean Code: Writing Code for Humans.
• How to see whitespace in SQL Server Management Studio.
• Aaron Bertrand shows various ways to solve the running totals problem. I borrowed his cursor method and adapted it for my demo.
• The Zen of Python. It may not always apply to T-SQL, but good advice is often language-agnostic.

Tools I Use

• Draw.IO for diagramming
• dbForge SQL Complete for formatting and refactoring T-SQL code