Analysis Rules

SQL Enlight users can create their own groups and custom analysis rules.
For more details see Manage Analysis Rules topics.

Categories
All rules

EX0004 : Find identifier references inside the T-SQL script

EX0005 : Check script for data modifying statements – INSERT,UPDATE,DELETE or EXECUTE

EX0006 : Identify possible missing Foreign Keys

EX0007 : List all DML and DDL triggers in current database

EX0009 : Consider adding proper comment block before each database object create statement

EX0010 : Identify missing indexes using dynamic management views information

EX0011 : Identify inefficient indexes using dynamic management views information

EX0012 : Displays memory usage information for the current database

EX0013 : Identify fragmented indexes that need rebuilding or re-indexing

EX0014 : List the last execution status of all available SQL Server jobs

EX0015 : Find best clustered index

EX0018 : Analyze execution plan and check for high cost operations

SA0001 : Equality and inequality comparisons involving a NULL constant found. Use IS NULL or IS NOT NULL

SA0002 : Variable declared but never referenced or assigned

SA0003 : Variable used but not previously assigned

SA0004 : Variable assigned but value never used

SA0005 : Non-ANSI outer join syntax

SA0006 : Non-ANSI inner join syntax

SA0007 : Pattern starting with “%” in LIKE predicate

SA0008 : Deprecated syntax string_alias = expression

SA0009 : Consider using a table variable instead temporary table

SA0010 : Use TRY..CATCH or check the @@ERROR variable after executing data manipulation statement

SA0011 : SELECT * in stored procedures, views and table-valued functions

SA0012 : Use SCOPE_IDENTITY() instead @@IDENTITY

SA0013 : Avoid returning results in triggers

SA0014 : Avoid ‘fn_’ prefix when naming functions

SA0015 : Avoid ‘sp_’ prefix when naming stored procedures

SA0016 : Use of very small variable length type (size 1 or 2)

SA0017 : SET NOCOUNT ON option in stored procedures and triggers

SA0018 : Support for constants in ORDER BY clause have been deprecated

SA0019 : TOP clause used in a query without an ORDER BY clause

SA0020 : Always use a column list in INSERT statements

SA0021 : Deprecated usage of table hints without WITH keyword

SA0022 : Index type (CLUSTERED or NONCLUSTERED) not specified

SA0023 : Avoid using not equal operator (<>,!=) in the WHERE clause

SA0024 : Local cursor not closed

SA0025 : Local cursor not explicitly deallocated

SA0026 : Local cursor variable not explicitly deallocated

SA0027 : Avoid wrapping filtering columns within a function in the WHERE clause

SA0028 : Function call can be extracted from the WHERE clause to avoid unnecessary table scan

SA0029 : Input parameter never used

SA0030 : Output parameter never assigned

SA0031 : Avoid GOTO statement to improve readability

SA0032 : Avoid using NOT IN predicate in the WHERE clause

SA0033 : Do not use the GROUP BY clause without an aggregate function

SA0034 : Use parentheses to improve readability and avoid mistakes because of logical operator precedence

SA0035 : TODO,HACK or UNDONE phrase found in a comment

SA0036 : DELETE statement without row limiting conditions

SA0037 : UPDATE statement without row limiting conditions

SA0038 : The comparison expression evaluates to TRUE

SA0039 : The comparison expression evaluates to FALSE

SA0040 : Consider moving the column reference to one side of the comparison operator in order to use the column index

SA0041 : Avoid joining with views

SA0042A : Avoid using special characters in object names

SA0042B : Avoid using special characters in object names

SA0043A : Avoid using reserved words for type names

SA0043B : Avoid using reserved words for type names

SA0044 : Consider creating indexes on all columns included in foreign keys

SA0045 : Consider updating statistics as they appear outdated and may mislead the query optimizer

SA0046 : Consider creating statistics on all composite index columns

SA0047 : Consider indexing the column as it is used in a WHERE clause or JOIN condition

SA0048 : Table does not have a primary key or unique key

SA0049 : Table does not have a clustered index

SA0050 : Do not create clustered index on UNIQUEIDENTIFIER columns

SA0051 : The query is missing a join predicate. This may affect or result more than expected rows

SA0052 : Avoid using undocumented and deprecated stored procedures

SA0053A : Don’t use deprecated TEXT,NTEXT and IMAGE data types

SA0053B : Don’t use deprecated TEXT,NTEXT and IMAGE data types

SA0054 : Avoid modification of parameters in a stored procedure prior to use in a query

SA0055 : Consider indexing the columns referenced by IN predicates in order to avoid table scans

SA0056 : Index has exact duplicate or overlapping index

SA0057 : Consider using EXISTS predicate instead of IN predicate

SA0058 : Avoid converting dates to string during date comparison

SA0059A : Check database for objects created with different than default or specified collation

SA0059B : Check for usage of collation different than the database default or the specified collation

SA0060 : The sp_xml_preparedocument procedure call is not paired with a following sp_xml_removedocument call

SA0061A : Check all Tables in the current database for following specified naming convention

SA0061B : Check table names used in CREATE TABLE statements for table name following specified naming convention

SA0062A : Check all Functions in the current database for following specified naming convention

SA0062B : Check function names used in CREATE FUNCTION statements for following specified naming convention

SA0063A : Check all Views in the current database for following specified naming convention

SA0063B : Check view names used in CREATE VIEW statements for following specified naming convention

SA0064A : Check all Stored Procedures in the current database for following specified naming convention

SA0064B : Check stored procedure names used in CREATE PROCEDURE statements for following specified naming convention

SA0065A : Check all Triggers for following specified naming convention

SA0065B : Check trigger names used in CREATE TRIGGER statements for following specified naming convention

SA0066A : Check all Columns for following specified naming convention

SA0067A : Check all Unique Key Constraints in the current database for following specified naming convention

SA0068A : Check all Check Constraints in the current database for following specified naming convention

SA0069A : Check all Default Constraints in the current database for following specified naming convention

SA0070A : Check all Primary Key Constraints in the current database for following specified naming convention

SA0071A : Check all Foreign Key Constraints in the current database for following specified naming convention

SA0072A : Check all Non-Key Indexes in the current database for following specified naming convention

SA0073A : Check all User-Defined Types in the current database for following specified naming convention

SA0074A : Check all Schema-s in the current database for following specified naming convention

SA0075 : Avoid constraints created with system generated name

SA0076 : Check UPDATE and DELETE statements for not filtering using all columns of the table’s PRIMARY KEY or UNIQE KEY

SA0077 : Avoid executing dynamic code using EXECUTE statement

SA0078 : Statement is not terminated with semicolon

SA0079 : Avoid using column numbers in ORDER BY clause

SA0080 : Do not use VARCHAR or NVARCHAR data types without specifying length

SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale

SA0082 : Consider prefixing column names with table name or table alias

SA0083 : Consider proactively checking the logical and physical integrity of all the objects in the database

SA0084 : Data purity check is not enabled for the current database

SA0085 : Check database objects for missing specific extended properties

SA0050B : Do not create clustered index on UNIQUEIDENTIFIER columns

SA0086 : Avoid storing database backups on the same volume as the database’s data files

SA0087 : Database has suspect pages and needs to be checked

SA0088 : The last full backup for the database cannot be found on the location where it was initially created

SA0090 : SQL Server password policy is vulnerable for login

SA0092 : The SQL module was created with ANSI_NULLS and/or QUOTED_IDENTIFIER options set to OFF

SA0093 : The compatibility level of the database is lower than the SQL Server version default compatibility level

SA0094 : Authentication set to Mixed Mode

SA0096 : The collation of the current database does not match that of the model database

SA0098 : The results from triggers are currently allowed. Consider disabling results from triggers

SA0099 : The database is using Full Recovery Model, but its last transaction log backup is too old

SA0100 : Database backups are outdated

SA0101 : Avoid using hints to force a particular behavior

SA0102 : Do not use DISTINCT keyword in aggregate functions

SA0103 : Avoid using ISNUMERIC function as it accepts floating point and monetary number

SA0048B : The table is created without a a primary key

SA0049B : The table is created without a clustered index

SA0089 : The option has a not recommended value SET which will cause the stored procedure to be recompiled

SA0091 : Setting the QUOTED_IDENTIFIERS or ANSI_NULLS options inside stored procedure, trigger or function will have no effect

SA0092B : The SQL module was created with ANSI_NULLS and/or QUOTED_IDENTIFIER options set to OFF

SA0095 : The updated column is a primary key column

SA0097 : The procedure/function/trigger has cyclomatic complexity above the threshold value

SA0104 : Use CASE statements in conjunction with aggregation to write more robust and better performing queries

SA0105 : Avoid using CHARINDEX function

SA0106 : Avoid OR operator in queries

SA0107 : Avoid using procedural logic with a cursor

SA0108 : Avoid using NOLOCK hint, use isolation levels instead

SA0109 : Avoid joining with subquery which has a TOP clause

SA0110 : Avoid have stored procedure that contains IF statements

SA0111 : Do not use WAITFOR DELAY/TIME statement in stored procedures, functions, and triggers

SA0112A : Avoid IDENTITY columns unless you are aware of their limitations

SA0112B : Avoid IDENTITY columns unless you are aware of their limitations

SA0113 : Do not use SET ROWCOUNT to restrict the number of rows

SA0114 : Duplicate names of objects found

SA0114B : Object with the same name but different type already exists

SA0115 : Ensure variable assignment from SELECT with no rows

SA0116 : Consider using EXISTS,IN or JOIN when usage of = (SELECT * FROM ) and the subquery returns more than column

SA0117 : Use OUTPUT instead of SCOPE_IDENTITY() or @@IDENTITY

SA0118 : Use MERGE instead of INSERT…UPDATE or UPDATE…INSERT statements

SA0119 : Consider aliasing all table sources in the query

SA0120 : Consider using NOT EXISTS,EXCEPT or LEFT JOIN instead of the NOT IN predicate with a subquery

SA0121 : Output parameter is not populated in all code paths

SA0122 : Use ISNULL(Column,Default value) on nullable columns in expressions

SA0123 : Consider replacing the OUTER JOIN with EXISTS

SA0124 : Columns in COALESCE are not all the same data type

SA0125 : Avoid use of the SELECT INTO syntax

SA0126 : Operator combines two different types will cause implicit conversion

SA0127 : Avoid wrapping filtering columns within a function in the WHERE clause or JOIN clause

SA0128 : Avoid using correlated subqueries. Consider using JOIN instead

SA0129 : Use WITH EXECUTE AS clause for stored procedures executing dynamic SQL

SA0131 : High number of estimated rows found in execution plan

SA0132 : The arguments of the ISNULL function are not of the same data type

SA0133 : Consider storing the result of the Date-Time function which get current time in a variable at the beginning of the statement and use these variable later

SA0134 : Do not interleave DML with DDL statements. Group DDL statements at the beginning of procedures followed by DML statements

SA0135 : Found filtering columns wrapped inside User-Defined Function call

SA0136 : Use fully qualified object names in SELECT, UPDATE, DELETE, MERGE and EXECUTE statements

SA0137 : BEGIN TRANSACTION statement is missing a following COMMIT statement

SA0138 : BEGIN TRANSACTION statement without ROLLBACK statement

SA0139 : The procedure argument type is not compatible with the procedure parameter type

SA0140 : Reserved keyword is not in the required case

SA0141 : Database is using Simple Recovery Model

SA0142 : Consider disabling CLR if user assemblies are not used in your environment

SA0143 : Single use Ad-hoc plans are using considerable amount of the procedure cache

SA0144 : The code following the RETURN or the RAISERROR statements will never be executed

SA0145 : The EOL marker sequence is not the expected {CR}{LF}

SA0146 : The RAISERROR statement with severity above 18 and requires WITH LOG clause

SA0147 : The Cognitive Complexity of the statement should not be too high

SA0148 : Consider using a temporary table instead of a table variable

SA0130 : Explicit error handling for statements between BEGIN TRAN and COMMIT/ROLLBACK TRAN is required

SA0149 : Consider using RECOMPILE query hint instead of WITH RECOMPILE option

SA0150 : The procedure grants permissions at the end of its body. Possible missing GO batch separator command

SA0151 : Statements appear after procedures main BEGIN/END block. Possible missing GO command

SA0152 : THROW statement appears as a transaction name in ROLLBACK TRANSACTION

SA0153 : Always specify parameter names when calling stored procedures

SA0154 : Constraint is not trusted

SA0154B : Constraint not checked and left not trusted

SA0155 : Deprecated setting of database option CONCAT_NULL_YIELDS_NULL to OFF

SA0155B : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated

SA0156 : Statements CREATE/DROP DEFAULT are deprecated. Use DEFAULT keyword in CREATE/ALTER TABLE

SA0157 : Usage of three and four part column names is deprecated. Two-part names is the standard-compliant behavior

SA0158 : Deprecated usage of space as separator for table hints. Use a comma instead of space

SA0159 : Deprecated use of object name containing only # characters

SA0161 : Current database uses old SQL Server collation. To take full advantage of SQL Server features, for new development change the default installation settings to use Windows collations

SA0160 : Deprecated use of @, @@, or names that begin with @@ as Transact-SQL identifiers

SA0162 : Column created with option ANSI_PADDING set to OFF

SA0163 : Deprecated setting of database options ANSI_PADDING to OFF

SA0163B : Setting ANSI_PADDING to OFF is deprecated

SA0164 : Consider adding WITH(NEXPAND) when querying an indexed view in order to enable query optimizer use view’s index

SA0066B : Check all Columns for following specified naming convention

SA0067B : Check all Unique Key Constraints for following specified naming convention

SA0068B : Check all Check Constraints in the current sql script for following specified naming convention

SA0069B : Check all Default Constraints in the current script for following specified naming convention

SA0070B : Check all Primary Key Constraints in the current sql script for following specified naming convention

SA0071B : Check all Foreign Key Constraints for following specified naming convention

SA0072B : Check all Non-Key Index for following specified naming convention

SA0073B : Check all User-Defined Types for following specified naming convention

SA0074B : Check all Schema-s for following specified naming convention

SA0075B : Avoid adding constraints with default system generated name

SA0165 : TOP (100) PERCENT found

SA0166 : Avoid altering security within stored procedures

SA0167 : Non-ISO standard comparison operator found

SA0168 : Possible division by zero not handled according the practice

SA0169 : Use @@ROWCOUNT only after SELECT, INSERT, UPDATE, DELETE or MERGE statements

SA0170 : It is recommend to not use CTE unless it is need for hierarchical data

SA0171 : The ROW_NUMBER paging pattern can be replaced with OFFSET FETCH clause

SA0172 : The dynamic SQL is constructed using external parameters, which is not ensured to be safe

SA0173 : COALESCE, IIF, and CASE input expressions containing sub-queries will be evaluated multiple times

SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates

SA0175 : Extract input expression as a variable in order to ensure it is invariant and avoid unexpected results

SA0176 : Consider merging nested IF statements to improve readability

SA0177 : To improve code readability, put only one statement per line

SA0178 : LIKE operator is used without wildcards

SA0179 : Do not create function and procedures with too many parameters

SA0180 : CASE expression has too many WHEN clauses

SA0181 : The query joins too many table sources

SA0182 : The CASE expressions is missing ELSE clause

SA0183 : The commented out code reduces readability and should be deleted

SA0184 : Redundant pairs of parentheses can be removed

SA0185 : Review the call for unintentionally passing the same value more than once as an argument

SA0186 : Possible missing BEGIN..END block

SA0187 : Duplicated string literals complicate the refactoring

SA0188 : The NULL or NOT NULL constraint not explicitly specified in the table column definition

SA0189 : Store procedure executed without getting a result

SA0190 : Numbered stored procedures are deprecated

SA0191 : Procedure body is not enclosed in BEGIN…END block

SA0192 : Procedure returns more than one result set

SA0193 : Avoid unused labels to improve readability

SA0194 : The ELSE clause is not needed.If it is omitted the CASE expression will still return NULL as default value

SA0195 : Duplicate statistics must be removed

SA0196 : Deprecated use of DROP INDEX with two-part index name syntax

SA0197 : The deprecated FASTFIRSTROW hint was encountered

SA0198 : Usage of deprecated GROUP BY ALL syntax encountered

SA0199 : Usage of deprecated COMPUTE clause encountered

SA0200 : Backup to tape syntax is deprecated

SA0201 : Textpointers statements WRITETEXT, UPDATETEXT and READTEXT are deprecated

SA0202 : The text and image functions TEXTPTR and TEXTVALID are deprecated

SA0203 : A deprecated system function is used

SA0204 : The system catalog view is deprecated and may be removed in a future version of SQL Server

SA0205 : The backward compatibility views for SQL Server 2000 system tables are deprecated. Use the current SQL Server system views instead

SA0206 : The sp_configure store procedure executed with a deprecated option

SA0207 : Setting ANSI_NULLS to OFF is deprecated

SA0208 : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated

SA0209 : SET OFFSETS is deprecated

SA0210 : Setting FMTONLY option is deprecated

SA0211 : Setting REMOTE_PROC_TRANSACTIONS option is deprecated

SA0212 : The SETUSER is deprecated. It is recommended to use EXECUTE AS instead

SA0213 : The DBCC command is deprecated

SA0214 : The CREATE TABLE, ALTER TABLE, or CREATE INDEX syntax without parentheses around the options is deprecated

SA0215 : The CREATE RULE and DROP RULE statements are deprecated

SA0216 : The TORN_PAGE_DETECTION option of ALTER DATABASE is deprecated. Use the PAGE_VERIFY option instead

SA0217 : Usage of GRANT,DENY and REVOKE statement with ALL option is deprecated

SA0218 : The ‘::’ function calling syntax is deprecated

SA0219 : A deprecated build-in function is used

SA0220 : Creating backups with PASSWORD or MEDIAPASSWORD option is deprecated

SA0221 : The FOR SOAP option in CREATE/ALTER ENDPOINT statement is deprecated

SA0222 : The ALTER LOGIN WITH SET CREDENTIAL or ALTER LOGIN WITH NO CREDENTIAL syntax is deprecated

SA0223 : The MODIFY FILEGROUP READONLY and READWRITE syntax is deprecated

SA0224 : The hint is deprecated for use on tables that are targets of INSERT statement

SA0225 : The usage of the NOLOCK and READUNCOMMITTED table hints is are deprecated in UPDATE, DELETE and MERGE statements

SA0226 : The usage of ROWGUIDCOL and IDENTITYCOL as column name in DML statements is deprecated

SA0227 : The TIMESTAMP syntax is deprecated, use ROWVERSION instead

SA0228 : WITHIN is now a reserved keyword. References to objects or columns named ‘within’ will fail

SA0229 : This syntax of RAISERROR is discontinued. Rewrite the statement using the current RAISERROR(…) syntax or consider using THROW

SA0230 : Identifier uses different case than object’s actual name

SA0231 : The used parameter or variable has different case than its declaration

SA0232 : The GO batch terminator command found inside comment

SA0233 : Temporary table created but not dropped

SA0234 : It is recommended to use the new TOP(expression) clause syntax

SA0235 : Consider using the AS keyword to specify a column alias instead of the column_alias = expression syntax

SA0236 : The xp_cmdshell system stored procedure used

SA0237 : Ordering of the result set before inserting it into a table is pointless

SA0238 : The user-defined function appearing in the query filter can cause performance problems

SA0239 : Setting the FORCEPLAN option to ON is not recommended

SA0240 : The stored procedure does not return result code

SA0241 : Check transaction and savepoint names for following specified naming convention

SA0242 : COUNT aggregate function used instead of EXISTS

SA0243 : Avoid INSERT-EXECUTE in stored procedures

SA0244 : Database object created,altered or dropped without specifiying schema name

SA0245 : Do not use ORDER BY to order the result set in view or inline table-valued function

SA0246 : Stored procedure executed with incorrect arguments

SA0248 : Stored procedure called with mixing both unnamed and named arguments style

SA0249 : Specify default value for columns added with NOT NULL constraint

SA0250 : Consider calling procedures with named arguments

SA0251 : Subquery used in expression not ensured to return a single value

SA0252 : The referenced object (table, view, procedure or function) is in another database

SA0253 : The current database is hardcoded in object reference

SA0254 : Invalid operation due to cursor closed or not declared

SA0255 : Consider using extended cursor declaration syntax instead of the ISO syntax

SA0256 : A cursor with the same name is declared earlier. Avoid reusing cursor names

SA0257 : The cursor declaration does not fit the performed cursor operations

SA0258 : The number of FETCH statement variables does not match the number of columns in the cursor definition

SA0259 : The created object already exists

SA0260 : Parameter defined as nullable, but no default value provided

SA0261 : The number of characters per line should not exceed the configured value

SA0262 : Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

SA0263 : Temporary table is used before it has any data inserted

SA0264 : Temporary table created but not used as table source

SA0265 : COMMIT statement without corresponding BEGIN TRANSACTION statement

SA0266 : ROLLBACK statement without corresponding BEGIN TRANSACTION statement

SA0267 : Table variable is used before it has any data inserted

SA0268 : Table variable is not used as table source

SA0269 : Datatype identifier is not in the required case

SA0270 : A filtered index created with the IS NULL predicate is not used in SQL Server

SEM001 : Comment lines count

SEM002 : Code lines count

SEM003 : Statements count

SEM004 : Cyclomatic complexity

SEM005 : Cognitive complexity

SEM006 : Count of SQL modules

SEM007 : Statement metrics for detecting duplicated code

SEM008 : List all tokens in the batch

See Also