Improving Database Code with Static Analysis
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 (T-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 T-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 T-SQL code if the rules in Visual Studio Team System Database Edition do not find all of the common issues that your team encounters. |
Related Scenarios
- Detecting and Correcting Managed Code Defects
Describes how to detect and correct code defects by using a code analysis tool for managed code.
See Also
Concepts
Extending the Features of Database Edition