Have you ever gone through the process of checking your SQL code for errors and typos? The painstaking process can be a drag, but it’s essential for maintaining code quality. If you’re like many database professionals, you’ll almost certainly have to go through the checking process over the course of your career. Luckily, there are many helpful resources to make the process as painless as possible.
We’ve compiled a checklist of common issues you should look out for when reviewing your code. It may seem like a lot to handle at first, but once you have a system in place, it becomes much easier. You’ll be able to catch a lot more errors and typos over time. This will save you time and frustration, so keep reading to learn more about how to do it.
What to review
Before we look at the different things you can review, let’s talk about what not to review. Most of the time, you’ll have to go through code reviews for new code additions. If you’ve just been handed a large project and need to check every line of code, you’re going to spend a lot of time reviewing code. Instead, focus on the things we’ll talk about below.
You shouldn’t review the code of older projects you’ve finished on. You might be tempted to review every line of code in your old projects, but that’s a huge waste of time. If someone else comes to you with a new project, hand them a high-level overview of what your code does and what it does not do. They can then ask questions and make changes as needed.
General Practices
When it comes to code reviews, there are a few practices you should always incorporate into your work culture. Get this right, and you’ll find it easy to review code and catch errors.
- Create a Code Review Policy – A code review policy is essential to help you focus on code quality. It can include things like a code review process, who can review code, and deadlines. You want to make sure everyone follows the policy, so make sure you have one in place.
- Review Code Using an Issue Tracker – You may love the task of checking code, but you probably don’t love reviewing code for days or weeks on end. Instead of reviewing code daily, monthly, or quarterly, assign issues to review code in a designated issue tracker. Let other people know what you’re doing and let them know when you’ve checked in with someone. You’ll get more thorough reviews if you give people an opportunity to review code as well.
- Use a Pull Request Process – If you have a code review policy and issues tracking system in place, you can hook them up with a pull request process. This means you can assign issues to people and have them assign pull requests. This can cut down on the number of reviews you have to do, while still keeping everyone accountable.
Step One: Organize Your Environment
There are a few things you’ll want to do before you get started with code reviews. First, you’ll want to make sure your environment is organized. You don’t want to spend your time looking at the wrong things or getting distracted. Make sure your code is stored (and versioned) in a way that makes it easy to find.
If you use source control, make sure it’s set up properly. Don’t use a system that doesn’t have a good history of keeping your code safe. You also want to make sure you store any documentation that pertains to your code in a way that’s easy to find.
If you use a code review service, make sure you have your environment set up properly. You might be missing things, such as issues or documents.
Step Two: Run a TSQL Formatter to Check for Errors
The first thing you’ll want to do is run a formatter. This is a preparatory step that makes your code easy to read. You’ll be able to easily pick out simple errors. Here are some links to formatting tools:
Step Three: Check for Basic issues
The next thing you’ll want to do is validate query syntax with SQL Server Management Studio (SSMS) using query syntax checker. Highlight your query in the query panel, then click the parse button or press Ctrl+F5 key combination.
This merely checks syntax; it doesn’t verify whether the objects you’ve referenced exist, or whether joins are legal, or anything else.
This will be an open-ended script that checks open-ended issues in your code. You can use a similar script for closed-ended issues, if you’d like. Make sure you follow the same process for closed-ended issues as well.
Step Four: Check for Deeper issues
The next thing you’ll want to do is run an automated tool to check for deeper issues. This means things like code that is not performant, an asterisk in a SELECT or using deprecated SET options, etc. This will help you make sure you’re following best practices.
I used to use a free SSMS plugin called SQL Code Guard. The add-in can run analysis on scripts to detect possible code issues, as well as display the dependencies for a database object. However, the company was bought by Redgate and its functionality was rolled into SQL Prompt. Since I don’t use SQL Prompt, I discovered Redgate still offers SQL Code Guard on the SQL Server command line for free. The checks are broken into rule categories:
- Best practice rules — Adhere to good industry practices.
- Deprecated syntax rules — T-SQL keywords or syntax discontinued by Microsoft.
- Execution rules — Identify issues that may be problematic upon execution.
- Miscellaneous rules — Rules that don’t fall under one of the other categories.
- Naming convention rules — Naming convention rules
- Performance rules — Use of SQL that could cause performance problems.
- Script rules — Issues to do with the SQL script and not the SQL itself.
- Style rules — Code style issues.
Of course there are paid SSMS plugins you can use to that make the review process easier:
Final Words
Congratulations! You’ve made it to the end of this article. You’ve learned how to check your code and keep it as high-quality as possible. You’ve also learned how to incorporate practices into your development environment that will help you check code more quickly and thoroughly.
Now it’s time to put these tips into practice. Start by checking one small thing today, and make that your way of doing things moving forward. From there, you can build up to larger projects.