My working definition is "rotten" code: code which has not been maintained very well. This code often lacks tests, but the key characteristic is the amount of pain necessary to make changes.
Legacy SQL will de-motivate you over time. You will spend too much time debugging terrible procedures. In order to fulfill even simple business requests, you will change lots of difficult code, including code you don't even know is obsolete. Even after you leave, your successor will curse your name.
Older code doesn't need to be legacy code. Older code does need TLC, however.
You need source control for your SQL code. Source control gives you the ability safely to experiment. If your experiment fails, roll back your changes.
Use whatever source control system your developers are using, which is probably something involving Git.
Object-oriented languages have fully-featured test frameworks like NUnit to create unit tests. Developers can break apart small sections of functionality and test those sections controlling for that code's depdendencies.
T-SQL has a very limited version of database unit and integration testing. The most popular tools for these kinds of tests are tSQLt and Visual Studio database test projects.
Formatting SQL is the lowest-risk form of refactoring, as you aren't making any logical code changes. If you format your SQL well, however, you can make your code more readable. Readable code is (potentially) understandable code.
Code is for humans as much as it is for compilers and interpreters!
Simple is better than complex.
Complex is better than complicated.
A few examples of complicated code:
We will now look at a series of methods to simplify code.
Once we have solid tests in place, we can look at refactoring entire designs instead of simply refactoring code within a single procedure.
This type of scenario will be most common if you have nested stored procedures. Then, treat the internal procedures as the equivalent of private methods and refactor around a contract.
Retain your outward-facing procedures' input and output signatures, and focus mostly on refactoring those "private" procedures.
Code is a liability: every line of code requires somebody to maintain it; code takes time to parse, compile, interpret, or run; and more mental overhead for developers.
If that line of code is not solving a problem, get rid of it!
Example: eliminating 20% of the code base with zero effect on users
We tend to hoard code, saving things "just in case." Instead of that, use source control and let a server hoard behind the scenes. Be aggressive in cleaning up code in comments or which is logically impossible.
Don't forget to take advantage of new functionality in SQL Server: window functions instead of cursors for running totals, data types like DATE, and functions like CONCAT to put together strings.
The net result from these changes is:
Legacy code is painful. Get tests in place and refactor that code.
Also, be sure you are keeping up on major changes in T-SQL as you upgrade to newer versions of SQL Server.
To learn more, go here:
Catallaxy Services consulting: