Working Effectively with Legacy SQL
WARNING!
This presentation is now obsolete. The information in this talk has not been updated in a while and could be outdated. Use the information in this presentation at your own risk and be sure to double-check things to ensure that any information you use is accurate.ABSTRACT
A legacy code base can be a frightening thing. Between strict deadlines, ever-evolving requirements, differing skill levels of contributors over time, and historical circumstances, keeping database code clean and concise is difficult at best. This example- and demo-driven talk will help you build out a mental framework to prune those gnarled code bases. Much of this talk will be familiar to software developers who know of the "clean code" philosophy, but no knowledge of the topic is necessary. If you shudder whenever you look at your SQL code base, this talk may be for you.
ADDITIONAL MEDIA
No recordings or additional media are available for this talk.SLIDES
Click here to access the slides for this presentation.
The slides are licensed under Creative Commons Attribution-ShareAlike.
DEMO CODE
Click here to access demo code for this presentation.
The source code is licensed under the terms offered by the GPL.
LINKS & FURTHER INFO
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