Analyzing Database Code to Improve Code Quality

  • Article

You can eliminate potential design and naming problems and avoid performance pitfalls by analyzing your database code. The concepts are very similar to performing static analysis to detect and correct defects in managed code. You configure which analysis rules you want to apply to your database code, analyze the code, and then correct or ignore the issues that you identify. Before you can analyze your database code, you must first import your database schema into a database project. For more information, see Starting Team Database Development.

By performing static analysis, you can identify problems that fall into the following categories:

  • Transact-SQL Design Issues
    Design issues include code that might not behave the way in which you expect, deprecated syntax, and issues that could cause problems when the design of your database changes.

  • Transact-SQL Naming Issues
    Naming issues arise if the name of a database object might cause unexpected problems or violate generally accepted conventions.

  • Transact-SQL Performance Issues
    Performance issues include code that might noticeably reduce the speed in which database operations are completed. Many of these issues identify code that will cause a table scan when the code is executed.

Any warnings or errors appear in the Error List. You can suppress an instance of a warning if you have determined that the issue does not have to be fixed. For example, you might decide not to fix a potential table scan if the table will never contain more than a few rows.

Common Tasks

The following illustration shows the configuration of code analysis rules and the results of a sample analysis.

Configuration and Results of Static Analysis on Database Code

Database Code Analysis Configuration and Results

In the following table, you can find descriptions of common tasks that support this scenario and links to more information about how you can successfully complete those tasks.

Common Tasks

Supporting Content

Learn by doing: You can learn about how to analyze your Transact-SQL code if you complete the walkthrough to analyze an existing database.

Configure rules for analyzing database code : You can configure which rules you want to apply to your database project for each build configuration. You can analyze your project after every successful build of it.

Detecting and correcting errors and warnings: You can find issues in your Transact-SQL code and view the source code that contains those issues. You can analyze your code by using MSBuild. You can track issues that will be fixed later or by another member of your team by creating a work item.

Suppress warnings: You can suppress all instances of a specific warning in a file if you know that the warning does not apply to your code. If you later change your mind, you can stop suppressing that warning.

Create and distribute custom rules for code analysis: You can learn how to create and distribute custom rules for your team's Transact-SQL code if the rules in Visual Studio Premium do not find all of the common issues that your team encounters.

See Also

Concepts

Extending the Database Features of Visual Studio

Other Resources

API Reference for Database Features of Visual Studio