Supress SQL Warnings on a per File Basis in Database Project

Question

I have two SQL Server Database projects in a Visual Studio Solution. One of them is a legacy database which I am just using a database reference in my main database project. I have a view in my main database project similar to

SELECT *
FROM [$(ReferenceDb)].[dbo].[Table1] t
WHERE t.IndexedColumnId IN (1, 55, 99)

I am getting

SR0004: Microsoft.Rules.Data : A column without an index that is used as an IN predicate test expression might degrade performance.

The reference database has an index on this column. Please note this is simplified code for demonstration purposes. I'm actually joining on tables in the reference DB to tables in my main DB. If I move this simple code to the reference DB project I don't get any warnings. It appears like the main database project isn't picking up on the indexes in the reference database.

What I'd like to do is to suppress this warning; but I don't want to do it for the entire main database project. I only want to suppress it for certain SQL views/stored procedures, etc.

I'm looking for something similar to the in source suppression of warnings available in C# files like.

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA1801:ReviewUnusedParameters", MessageId = "args")]

but I need to be able to do this in the .sql files in the main database project.

I debated turning off the warnings for the entire project but really don't want to go that route.

Any suggestions on this issue?

Answer 1

Suppressing SQL Server Static Code Analysis warnings in SSDT on a per-file basis is supported in Visual Studio 2017 and Visual Studio 2019.

1. Create a new XML file in your SSDT project root named StaticCodeAnalysis.SuppressMessages.xml.

   • Ensure that it's also added to the .sqlproj file with a Build Action of None and not copied to the output directory.

2. Copy and paste this template into that file:

   <?xml version="1.0" encoding="utf-8"?>
   <StaticCodeAnalysis version="2" xmlns="urn:Microsoft.Data.Tools.Schema.StaticCodeAnalysis">
       <SuppressedFile FilePath="dbo\Stored Procedures\Procedure1.sql">
           <SuppressedRule Category="Microsoft.Rules.Data" RuleId="SR0007" />
           <SuppressedRule Category="Microsoft.Rules.Data" RuleId="SR0004" />
           <SuppressedRule Category="Microsoft.Rules.Data" RuleId="SR0005" />
           <SuppressedRule Category="Microsoft.Rules.Data" RuleId="SR0015" />
       </SuppressedFile>
       <SuppressedFile FilePath="dbo\Functions\Function1.sql">
           <SuppressedRule Category="Microsoft.Rules.Data" RuleId="SR0007" />
       </SuppressedFile>
   </StaticCodeAnalysis>
   

3. Rebuild your database project and those rules will now be suppressed in those files.

Warning: Ever since I updated to Visual Studio 2019 16.7 I've been having issues with SSDT where Visual Studio will often completely freeze and lock-up when I right-click an SSDT warning in the Error List window - or if that works then it will definitely freeze when I choose the suppression options in the context menu, so I have to manually add suppressions to this file by-hand, ugh.