Whenever I start a new project, I check to see what developers are using to manage and version control their database. When I find that they are doing nothing, I introduce SSDT:
- Create a new solution
- Import the existing database schema
- Add to Source Control
Optionally,
- Turn on Code Analysis
- Crawl under my desk and have a good cry
- Turn off Code Analysis
If you have not seen it, code analysis is a nice feature that you can find in the properties of an SSDT project:
It will look for common anti-patterns and flag them during a code analysis. The output window provides a description of the issues and will allow you to doubleclick an item an go directly to the offending script and line. Unfortunately, on an unmanaged database, these issues usually number in the hundreds to thousands. Some of the things that it is looking for is not necessarily that helpful or relevant, like looking for varchar(2) strings and saying they should be char(2). Other items can provide a lot of useful feedback.
Ratcheting
So how can we still take advantage of Code Analysis without being overwhelmed with defeat? We can use a technique called ratcheting, in which we pick out just a few items to analyze and attack the problem in bite-size pieces. In the example above, I chose, SELECT *, deprecated join syntax and sp_. A few things to note:
- This should be set for all build configurations
- Enable Code Analysis on Build should be checked
- Treat Warning as Error should be checked in order to cause the build to fail so the issue must be addressed before the code is checked in
Once the team has gotten comfortable with these first issues, a few weeks to one month later, we can crank the ratchet and add a few more. Little by little, we will have addressed all of the bits of code analysis that we are concerned with and we will have a much cleaner code base.
Labels: SSDT
