An editor extension that checks SQL code against industry best practices. Open SQL file in Visual Studio and click on ‘Execute SQL Code Check’ under ‘Code Check’ from Visual Studio ribbon. Warnings will be displayed in Output window.
Below are some of the rulesets validated by tool:
Use table aliases and refer to column names using that alias
Use WITH (NOLOCK) for selecting or deleting rows from a table
Use WITH (NOLOCK) in JOIN conditions
Use WITH (HOLDLOCK TABLOCKX) with UPDATE statements
Use SET NOCOUNT ON
Do not use DATA_COMPRESSION = PAGE flag for INDEXES on temp tables
Do not use LOCKS on Temporary Tables/Views
Follow Pascal case for variable/table/views/stored procedure names
Spaces are not allowed in TABLE/VIEW/PROCEDURE/Constraint names
Follow 'IXCU_ColumnName' convention for UNIQUE CLUSTERED INDEX
Follow 'IXC_ColumnName' convention for CLUSTERED INDEX
Follow 'IXU_ColumnName' convention for UNIQUE NONCLUSTERED INDEX
Follow 'IX_ColumnName' convention for NONCLUSTERED INDEX
Follow 'IXCS_ColumnName' for COLUMNSTORE INDEX
Follow 'PK_TableName_ColumnName' convention for PRIMARY KEY
Create Table/Primary Key/Clustered, Non-Clustered Index/ only on DATA File Group
Either DATA_COMPRESSION = PAGE flag is missing, or you have used flags other than DATA_COMPRESSION = PAGE for Indexes
Multiple calls of GETDATE() found. Store GETDATE() value in a variable and use this variable
Avoid use of subqueries in JOIN statements
Avoid use of subqueries in WHERE clause
Check headers for SQL file, like Mention Author, Created Date, Description and Test Scripts as comments at the beginning of a stored procedure
Use CONVERT() instead of CAST()
After successful installation of Code Check extension, it will be visible in your Visual Studio ribbon.
Click on Code Check -> SQL Code Check - > Execute SQL Code Check. You should have SQL file open in the IDE for the extension to report warnings:
Warnings are displayed in the output window:
Fix these warnings to be aligned with Industry development standards.
