SQL code analysis to improve code quality

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance SQL database in Microsoft Fabric

You can eliminate potential design and naming problems and avoid performance pitfalls by analyzing your database code. The concepts are 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 Start from an existing database.

By performing static analysis with the provided rules, you can identify problems that fall into the following Transact-SQL (T-SQL) categories:

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

• T-SQL naming problems: Naming problems arise if the name of a database object might cause unexpected problems or violate generally accepted conventions.

• T-SQL performance problems: Performance problems include code that might noticeably reduce the speed of database operations. Many of these problems identify code that causes a table scan when the code runs.

  

Code analysis rules are extensible. You can create your own rules to enforce your own coding standards. For more information, see Code analysis rules extensibility overview.

SQL project file sample and syntax

A SQL project file can contain two properties, RunSqlCodeAnalysis and SqlCodeAnalysisRules. The RunSqlCodeAnalysis element specifies whether to run code analysis when the project is built. By default, the build runs all included rules, and rule pattern detection results in a build warning.

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="1.0.0" />
  <PropertyGroup>
    <Name>AdventureWorks</Name>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
    <RunSqlCodeAnalysis>True</RunSqlCodeAnalysis>
  </PropertyGroup>
...

The SqlCodeAnalysisRules element specifies the rules and their error or warning behavior. In the following example, the rules Microsoft.Rules.Data.SR0006 and Microsoft.Rules.Data.SR0007 are disabled, and a detection for the rule Microsoft.Rules.Data.SR0008 results in a build error.

<?xml version="1.0" encoding="utf-8"?>
<Project DefaultTargets="Build">
  <Sdk Name="Microsoft.Build.Sql" Version="1.0.0" />
  <PropertyGroup>
    <Name>AdventureWorks</Name>
    <DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
    <ModelCollation>1033, CI</ModelCollation>
    <RunSqlCodeAnalysis>True</RunSqlCodeAnalysis>
    <SqlCodeAnalysisRules>-Microsoft.Rules.Data.SR0006;-Microsoft.Rules.Data.SR0007;+!Microsoft.Rules.Data.SR0008</SqlCodeAnalysisRules>
  </PropertyGroup>
...

You can add a StaticCodeAnalysis.SuppressMessages.xml file to the project to suppress specific code analysis findings. The following example suppresses the warning SR0001 for the stored procedure in the file StoredProcedures/uspGetEmployeeManagers.sql.

<?xml version="1.0" encoding="utf-8" ?>
<StaticCodeAnalysis version="2" xmlns="urn:Microsoft.Data.Tools.Schema.StaticCodeAnalysis">
  <SuppressedFile FilePath="StoredProcedures/uspGetEmployeeManagers.sql">
    <SuppressedRule Category="Microsoft.Rules.Data" RuleId="SR0001" />
  </SuppressedFile>
</StaticCodeAnalysis>

Provided rules

T-SQL design problems

When you analyze the T-SQL code in your database project, you might see one or more warnings categorized as design problems. Address these problems to avoid the following situations:

• Subsequent changes to your database might break applications that depend on it.
• The code might not produce the expected result.
• The code breaks if you run it with future releases of SQL Server.

In general, don't suppress a design problem because it might break your application, either now or in the future.

The provided rules identify the following design problems:

• SR0001: Avoid SELECT * in stored procedures, views, and table-valued functions
• SR0008: Consider using SCOPE_IDENTITY instead of @@IDENTITY
• SR0009: Avoid using types of variable length that are size 1 or 2
• SR0010: Avoid using deprecated syntax when you join tables or views
• SR0013: Output parameter (parameter) isn't populated in all code paths
• SR0014: Data loss might occur when casting from {Type1} to {Type2}

T-SQL naming problems

When you analyze the T-SQL code in your database project, you might see one or more warnings categorized as naming problems. Address these problems to avoid the following situations:

• The name you specify for an object conflicts with the name of a system object.
• You must always enclose the specified name in escape characters (for example in SQL Server, [ and ]).
• The name you specify confuses others who try to read and understand your code.
• The code breaks if you run it with future releases of SQL Server.

In general, suppress a naming problem if other applications that you can't change depend on the current name.

The provided rules identify the following design problems:

• SR0011: Avoid using special characters in object names
• SR0012: Avoid using reserved words for type names
• SR0016: Avoid using sp_ as a prefix for stored procedures

T-SQL performance problems

When you analyze the T-SQL code in your database project, you might see one or more warnings categorized as performance problems. Address a performance problem to avoid the following situation:

• A table scan occurs when you run the code.

In general, you can suppress a performance problem if the table contains so little data that a scan doesn't significantly affect performance.

The provided rules identify the following design problems:

• SR0004: Avoid using columns that do not have indexes as test expressions in IN predicates
• SR0005: Avoid using patterns that start with "%" in LIKE predicates
• SR0006: Move a column reference to one side of a comparison operator to use a column index
• SR0007: Use ISNULL(column, default_value) on nullable columns in expressions
• SR0015: Extract deterministic function calls from WHERE predicates

Enable and disable code analysis

dotnet build /p:RunSqlCodeAnalysis=False

dotnet build /p:RunSqlCodeAnalysis=True /p:SqlCodeAnalysisRules="+!Microsoft.Rules.Data.SR0001;+!Microsoft.Rules.Data.SR0008"

Related content

• Analyze T-SQL code to find defects
• T-SQL design issues
• T-SQL naming issues
• T-SQL performance issues
• Code analysis rules extensibility overview