tcartwright / sqlserver.rules

SQL Server static code analysis rules for SSDT database projects

Hi there 👋

I am Tim Cartwright, a DBA / Developer from Houston Texas

• 🔭 I’m currently working on the repos I maintain as well as my 9 to 5.
  • I tend to post a lot of loose sql files and powershell scripts to my GIST
• I have a variety of interests:
  • SQL Server
    • Administration
    • T-SQL and DB Optimization
  • Application and Web development
  • Devops
  • Administrative scripts and tools
• I am willing to collaborate on anything that piques my interest. Or accept collaboration on any of my repos. 😄

sqlserver.rules's Issues

Issue request form

make sure required info is present see github docs

Refactor docs generator to find .dll from csproj file

• The documentation generator should not need the hardcoded path to the DLL file. The values are available from the csproj file.
• Extend logging
• Add table to Table of Contents: Friendly Name | Ignorable | Message
• Modify docs generator script to take in the new $GITHUB_BUILD_OUTPUT_DLL variable and remove all hard coded references to "SQLServer.Rules.dll" or "SQLServer.Rules". Infer all those values from the path passed in.

Per project detailed documentation

Write detailed documentation for each project. Especially:

• SqlServer.Rules.SolutionGenerator
  • Demo report(s)
• SqlServer.Dac
• SqlServer.Rules
  • will be partly handled by the MD generator script

Add XML Docs to Design Rule Classes (3)

Add to these files:

• SqlServer.Rules/Design/DoNotMixDMLWithDDLRule.cs
• SqlServer.Rules/Design/DeleteWithoutWhereRule.cs
• SqlServer.Rules/Design/ConsiderEXISTSInsteadOfInRule.cs
• SqlServer.Rules/Design/ConsiderColumnPrefixRule.cs
• SqlServer.Rules/Design/CacheGetDateToVariable.cs
• SqlServer.Rules/Design/AvoidWidePKsRule.cs
• SqlServer.Rules/Design/AvoidViewJoinsRule.cs
• SqlServer.Rules/Design/AvoidSetRowcountRule.cs
• SqlServer.Rules/Design/AvoidSelectStarRule.cs
• SqlServer.Rules/Design/AvoidSelectIntoRule.cs
• SqlServer.Rules/Design/AvoidORInQueriesRule.cs
• SqlServer.Rules/Design/AvoidNumericOrderByRule.cs
• SqlServer.Rules/Design/AvoidIfInStoredProcRule.cs
• SqlServer.Rules/Design/AvoidHintsRule.cs
• SqlServer.Rules/Design/AvoidExecuteRule.cs
• SqlServer.Rules/Design/AvoidCursorsRule.cs
• SqlServer.Rules/Design/AvoidCHARTypeRule.cs
• SqlServer.Rules/Design/AvoidCHARINDEXRule.cs
• SqlServer.Rules/Design/AliasTablesRule.cs

Documenation builder

We can ticket out the work.

• powershell to extract the MD from the C# attributes, and xml comments.

Pulls from:

• Fill out summary tag
• FriendlyName, and IsIgnorable tags
• ExampleMd tag with good / bad examples

Originally posted by @tcartwright in #14 (comment)

Setup code formating

• rules / script
• look at enforcing on check in

Add XML Docs to the the Performance Class files (1)

Add docs the the following:

• SqlServer.Rules/Performance/UseExistenceInsteadOfCountRule.cs
• SqlServer.Rules/Performance/TableMissingClusteredIndexRule.cs
• SqlServer.Rules/Performance/SetNoCountOnRule.cs
• SqlServer.Rules/Performance/QueryHighJoinCountRule.cs
• SqlServer.Rules/Performance/NestedViewRule.cs
• SqlServer.Rules/Performance/ModifiedParameterRule.cs
• SqlServer.Rules/Performance/DoNotUseDistinctInAggregatesRule.cs
• SqlServer.Rules/Performance/DataTypesOnBothSidesOfEqualityRule.cs
• SqlServer.Rules/Performance/CursorNotDeallocatedRule.cs
• SqlServer.Rules/Performance/CursorNotClosedRule.cs
• SqlServer.Rules/Performance/ConsiderRecompileQueryHintRule.cs
• SqlServer.Rules/Performance/ConsiderIndexingInClauseColumnsRule.cs
• SqlServer.Rules/Performance/AvoidUpdatingPrimaryKeyColumnsRule.cs
• SqlServer.Rules/Performance/AvoidTableVariableInJoinsRule.cs
• SqlServer.Rules/Performance/AvoidReturningResultsFromTriggersRule.cs
• SqlServer.Rules/Performance/AvoidOuterJoinsRule.cs

Add XML Docs to Design Rule Classes (2)

Add doc comments to the following:

• SqlServer.Rules/Design/ObjectUsesDifferentCollationRule.cs
• SqlServer.Rules/Design/ObjectTwoPartNameRule.cs
• SqlServer.Rules/Design/ObjectCreatedWithInvalidOptionsRule.cs
• SqlServer.Rules/Design/NoPrecisionScaleRule.cs
• SqlServer.Rules/Design/NoLengthVarcharRule.cs
• SqlServer.Rules/Design/MissingJoinPredicateRule.cs
• SqlServer.Rules/Design/MismatchedColumnsRule.cs
• SqlServer.Rules/Design/InvalidDatabaseOptionsRule.cs
• SqlServer.Rules/Design/HighNumberOfIndexesRule.cs
• SqlServer.Rules/Design/FunctionTypeMismatchRule.cs
• SqlServer.Rules/Design/ForeignKeysNeedIndexRule.cs
• SqlServer.Rules/Design/EqualityCompareWithNULLRule.cs
• SqlServer.Rules/Design/DuplicateIndexesRule.cs
• SqlServer.Rules/Design/DoNotUseRealOrFloatRule.cs
• SqlServer.Rules/Design/DoNotUseNOLOCKRule.cs
• SqlServer.Rules/Design/DoNotUseIdentityFunction.cs
• SqlServer.Rules/Design/DoNotUseDeprecatedTypesRule.cs

NEW RULE IDEAS

NEW RULE IDEAS

• not for replication (Design) Added in rule SRD0065
• Constraints: check for
  • udf usage (Performance)
  • nocheck (Design)
• Suggest SCHEMABINDING for functions that do not touch tables (Performance)
• Detect sql injection possibilities???

Notes:

• Would like to only detect injection opportunies where sql is being concatenated in from a variable.
• Would also like to build a sql injection scanner for .Net code

Modify pipeline to create release on push

Add GitHub Build action

We can ticket out the work.

• Convert projects to SDK style (This works for all except the DB project, unless we use SDK Style SqlProj)
• Add build pipeline
  Originally posted by @tcartwright in #14 (comment)

Add XML Docs to Design Rule Classes (1)

Add docs to the following:

• SqlServer.Rules/Design/WrapStatementsWithTryCatchRule.cs
• SqlServer.Rules/Design/WrapStatementsWithTransactionRule.cs
• SqlServer.Rules/Design/WaitForRule.cs
• SqlServer.Rules/Design/UseProperCollationInTempTables.cs
• SqlServer.Rules/Design/UseParameterNamesWithProcsRule.cs
• SqlServer.Rules/Design/UseColumnListWithInsertsRule.cs
• SqlServer.Rules/Design/UpdateWithoutWhereRule.cs
• SqlServer.Rules/Design/UnusedVariablesRule.cs
• SqlServer.Rules/Design/UnusedParameterRule.cs
• SqlServer.Rules/Design/TypesMissingParametersRule.cs
• SqlServer.Rules/Design/TopWithoutOrderByRule.cs
• SqlServer.Rules/Design/TableHasUniqueConstraintRule.cs
• SqlServer.Rules/Design/TableHasPrimaryKeyRule.cs
• SqlServer.Rules/Design/StartIdentity1000Rule.cs
• SqlServer.Rules/Design/RaiseErrorWithoutLogRule.cs
• SqlServer.Rules/Design/ProcedureContainsGrantRule.cs
• SqlServer.Rules/Design/PredicateCompareRule.cs

Add XML Docs to the Performance Class files (2)

Add to these files:

• SqlServer.Rules/Performance/AvoidNotInRule.cs
• SqlServer.Rules/Performance/AvoidNotEqualToRule.cs
• SqlServer.Rules/Performance/AvoidFunctionsInActionQueries.cs
• SqlServer.Rules/Performance/AvoidEndsWithOrContainsRule.cs
• SqlServer.Rules/Performance/AvoidCorrelatedSubqueriesRule.cs
• SqlServer.Rules/Performance/AvoidColumnFunctionsRule.cs
• SqlServer.Rules/Performance/AvoidColumnCalcsRule.cs

Improve docs

lets get a basic readme

PowerShell Install script

Also an installer even if it's your PS script added to the root folder.

Originally posted by @matthew-n in #14 (comment)

Add CI for

Just the basics for now:

• branch per feature
• gate accepting pull request with unit test

Alter pipeline action path rules

Use this module: https://github.com/dorny/paths-filter#examples

to prevent unnecessary releases from being created.

Add XML Docs to the Naming Rule Classes

Add docs the the following:

• SqlServer.Rules/Naming/UseTwoPartNamesRule.cs
• SqlServer.Rules/Naming/NamingViolationRule.cs
• SqlServer.Rules/Naming/GeneralNamingRules.cs
• SqlServer.Rules/Naming/AvoidSPPrefixRule.cs
• SqlServer.Rules/Naming/AvoidFNPrefixRule.cs
• SqlServer.Rules/NamedTableView.cs

Update generate_docs.ps1 script

Update generate_docs.ps1 in the workflows folder to actually generate the docs. Last piece of the pipeline puzzle.

Unit tests for rules

Implement create unit test project(s) for the rules. Look at MS code sample as reference implementation

Add XML Docs to core clas files

Add docs to the following:

• SqlServer.Rules/SqlServerRule.cs
• SqlServer.Rules/ReferentialIntegrity/RIUtils.cs
• SqlServer.Rules/ReferentialIntegrity/JoinInfo.cs
• SqlServer.Rules/ReferentialIntegrity/ForeignKeyInfo.cs
• SqlServer.Rules/Globals/ModelInfo.cs
• SqlServer.Rules/Globals/Ignorables.cs
• SqlServer.Rules/Globals/Constants.cs
• SqlServer.Rules/BaseSqlCodeAnalysisRule.cs

Rules don't evaluate on Visual Studio 2022

I was working with a customer that was trying to implement these rules with Visual Studio 2022. When done through the VS GUI the rules were successfully evaluated, but when done from msbuild.exe, the rules were essentially ignored. The problem was determined to be that the SqlServer.Rules solution was compiled with DacFx 150. It appears this causes issues with the Microsoft Entity Framework such that when MEF tries to match the rules with the required condition, it can’t match the types in 150 and 160. We downloaded the source code of the SqlServer.Rules and compiled it using DacFx 160 and were able to get Visual Studio 2022 to recognize the custom rules and evaluate them when run from msbuild.exe.

If you want to make these custom rules compatible with VS 2022, please rebuild the solution with DacFx 160. This should be backwards compatible such that the new release still works with previous Visual Studio version as well.

Please see microsoft/DacFx#364 for reference.