Analyzing Database Code to Improve Code Quality

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

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.	Walkthrough: Analyze Transact-SQL Code for 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.	How to: Enable and Disable Static Analysis for Database Code How to: Enable and Disable Specific Rules for Static Analysis of Database Code
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.	How to: Analyze Transact-SQL Code to Find Defects How to: Create a Work Item from a Defect in Transact-SQL Code
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.	How to: Suppress Database Code Analysis Warnings How to: Stop Suppressing Warnings for Database Code Analysis
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.	Walkthrough: Authoring a Custom Static Code Analysis Rule Assembly for SQL How to: Distribute Custom Feature Extensions to Team Members

Related Scenarios

• Analyzing Managed Code Quality by Using Code Analysis
  Describes how to detect and correct code defects by using a code analysis tool for managed code.

See Also

Concepts

Extending the Database Features of Visual Studio

Other Resources

API Reference for Database Features of Visual Studio