SQL Enlight Guide

Analysis

Analysis Rules

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

EX0019 : Find references to non-existing objects and columns

EX0020 : Specified text found in comments

EX0021 : Check DROP TABLE statements in order to avoid unintentional data loss

EX0025 : Compressed column used without decompression or updated/inserted without compression

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

SA0048B : The table is created without a a primary key

SA0049 : Table does not have a clustered index

SA0049B : The table is created without a clustered index

SA0050 : Do not create clustered index on UNIQUEIDENTIFIER columns

SA0050B : 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

SA0066B : Check all Columns for following specified naming convention

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SA0075 : Avoid constraints created with system generated name

SA0075B : Avoid adding constraints with default 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

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

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

SA0090 : SQL Server password policy is vulnerable for login

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

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

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

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

SA0094 : Authentication set to Mixed Mode

SA0095 : The updated column is a primary key column

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

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

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

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

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

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 requried 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

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

SA0150 : The procedure grants itself permissions. Possible missing GO 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

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

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

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

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

SA0247A : Don’t use FLOAT, REAL, MONEY, SMALLMONEY or SQL_VARIANT data types

SA0247B : Don’t use FLOAT, REAL, MONEY, SMALLMONEY or SQL_VARIANT data types

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 requried case

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

EX0012 : Displays memory usage information for the current database

The topic describes the EX0012 analysis rule.

Message

Displays memory usage information for the current database

Description

The rule displays memory usage information for the current database using the dynamic management view sys.dm_os_buffer_descriptors.

Scope

The rule has a ContextOnly scope and is applied only on current server and database schema.

Parameters

Rule has no parameters.

Remarks

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Other Resources

Analysis Rules