Code analysis with SQL projects

SQL projects are a development capability for SQL Server and Azure SQL where your database objects are stored as code with integrations for CI/CD capabilities like code quality checks and dynamically calculated deployments. This article walks/runs through the fundamentals of code analysis with SQL projects and how it is useful both for local development and as a CI check.

⏸️ Before you get started

In a previous article, "SQL database projects intro", we created a SQL project with 2 tables and a stored procedure.

• dbo.Product (table)
• dbo.ProductOrder (table)
• dbo.GetProductInfo (stored procedure)

SQL database projects are built on top of the .NET SDK - you don't need to know .NET development to use them - but you do need the .NET 8 or higher SDK installed.

Want to quickly check if you have the .NET SDK installed?

dotnet --list-sdks

▶️ With the .NET SDK installed and a sample project created like the one from the intro article, you're ready to go.

📝 Create a new view

Add a new view [dbo].[All Products] in a .sql file (dbo.AllProducts.sql) in a new folder Views under project folder.

CREATE VIEW [dbo].[All Products] AS
SELECT *
FROM dbo.Product

Build the SQL project, verifying the object references and our SQL syntax is correct.

dotnet build

🛠️ Check our code quality

While SELECT * seems like a nice shortcut to reference all potential columns, it comes with risks of schema drift, inadvertently clogging up I/O with extra data, and more. In general - your code is more fragile in ways that aren't easily observed.

This is where SQL code analysis comes in handy, because it will check the database model created from our SQL project for detectable issues. We can run code analysis on our SQL project as a 1-off analysis with an extra property on the project build /p:RunSqlCodeAnalysis=true.

dotnet build /p:RunSqlCodeAnalysis=true

Now instead of the build succeeding with no warnings, we have two warnings:

1. warning SR0011: Microsoft.Rules.Data : Object name(All Products) contains special characters.
2. warning SR0001 Microsoft.Rules.Data : The shape of the result set produced by a SELECT * statement will change if the underlying table or view structure changes.

Getting ongoing analysis of our SQL code quality provides a quick feedback loop even before we send the code into a git repository. We can enable code analysis to run by default on every project build by incorporating that property into the project file itself with <RunSqlCodeAnalysis>. A sample project file with the code analysis setting added would look like:

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

🚧 Choose errors and warnings

Some code issues are worse than others - they might have a high impact or be difficult to change in the future. For these kinds of issues we can elevate the warning to an error that will cause our build to fail.

In our current project we're getting a warning that the object name [dbo].[All Products] contains a special character (the space). Before I fix this issue I want to make sure that these kinds of mistakes don't get ignored in the future.

The project property <SqlCodeAnalysisRules> can be used to exclude a rule completely or elevate a rule from a warning to an error. When choosing errors and warnings, the project property is a semicolon separated list with three settings available for each rule:

• elevate a rule from warning to error with +!
• no entry for the rule results in the rule throwing a warning
• demote a rule to no warning with - (disable)

The setting per rule is prefixed to the rule name. Let's turn the object name warning into an error message since names can be more difficult to change down the road. Right below the property <RunSqlCodeAnalysis> in our project file we add:

<SqlCodeAnalysisRules>+!Microsoft.Rules.Data.SR0011;</SqlCodeAnalysisRules>

If we build our project again using dotnet build we recieve one error and one warning:

1. error SR0011: Microsoft.Rules.Data : Object name(All Products) contains special characters.
2. warning SR0001 Microsoft.Rules.Data : The shape of the result set produced by a SELECT * statement will change if the underlying table or view structure changes.

⚠️ Note that when the build fails, the build artifact (.dacpac file) isn't updated. You need a successful build for your database model to be ready for deployment.

✏️ Fix the view name

Using the information in the error message, including the filename and line number, we'll fix the build error resulting from our modification to the code analysis warnings and errors. We should edit the view definition in dbo.AllProducts.sql to have a name without spaces, like [dbo].[AllProducts].

Once the view name is fixed, verify that your project now builds with a single warning message for SR0001 (SELECT *).

📍 Supress a specific code analysis finding

Code analysis rules provide a structure for evaluating your code, but at the end of the day its your project and you may have a reason for contradicting best practices. Instead of completely disabling a rule with the <SqlCodeAnalysisRules> project property, we can use the StaticCodeAnalysis.SuppressMessages.xml file to suppress a specific rule for a specific file.

Create a new file next to the SQL project file named StaticCodeAnalysis.SuppressMessages.xml. In this file we add an entry for the view file and the SELECT * rule.

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

If we use SELECT * in other files of our project the warning will appear for those instances, but we've specified that SELECT * is allowed in our view dbo.AllProducts.

Running dotnet build with this message suppression XML file in the project will result in no warnings being shown for our project now.

🧩 (optional) Incorporate additional code analysis rules

The small set of code analysis rules included with SQL projects are helpful, but there are quite a few community projects extending the code analysis rules. Incorporating these custom code analysis rulesets into your SQL project code analysis is quickly accomplished through package references.

A package reference is an entry in the SQL project file to a NuGet feed, like the community TSQLSmells on NuGet.org - https://www.nuget.org/packages/ErikEJ.DacFX.TSQLSmellSCA

We don't even need to manualy edit the .sqlproj because the .NET tooling has a terminal command to add packages:

dotnet add package ErikEJ.DacFX.TSQLSmellSCA --version 1.1.2

When we run dotnet build again, the build succeeds but we now have two new warnings!

1. warning SML005: Smells : Avoid use of 'Select *'
2. warning SML030: Smells : Include SET NOCOUNT ON inside stored procedures

While adding the custom rules to your SQL project takes just a few moments, the developers that work on the rulesets have invested countless hours of their time and share with the community. Remember to contribute to the projects that benefit your work as you are able.

🚀 (optional) Add a GitHub continuous integration (CI) pipeline

If we've pushed our project to a GitHub repository, we can utilize GitHub Actions to check our project for code analysis findings. This is a great way to automate watching for code smells in databases.

The pipeline definitions are stored in a .github/workflows folder. Create a new file code-analysis.yml in the .github/workflows folder. An example pipeline definition is shown below to get you started, but your branching strategy and code review process may vary.

# .github/workflows/code-analysis.yml
name: Run code analysis on SQL project
on:
  pull_request:
    branches:
    - main

jobs:
  build-with-analysis:
    runs-on: ubuntu-latest

    steps:
    - uses: actions/checkout@v4
    - name: Setup .NET
      uses: actions/setup-dotnet@v4
      with:
        dotnet-version: 8.0.x

    - name: Build with code analysis
      run: dotnet build -warnaserror ProductsTutorial.sqlproj /p:RunSqlCodeAnalysis=true

In this workflow we've added yet another flag to dotnet build so that all code analysis warnings are elevated to errors. This is useful when you are using code analysis as an optional check. If you're using code analysis as a required check (highly suggested) selecting specific rules to throw errors instead via the SqlCodeAnalysisRules project property is a better approach.

Guess what!? Your code analysis CI check is also doing the basic model validation for the database - ensuring you're not referencing objects that don't exist and the SQL syntax is valid. That's a win-win.

📚 Learn more

The tooling ecosystem around SQL projects includes VS Code, Visual Studio, and the SqlPackage CLI. Here are some additional resources to learn more about SQL projects for development and deployment:

• Microsoft.Build.Sql projects documentation
• SQL projects CI/CD sample repository
• SqlPackage for command line deployments