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
Click or drag to resize

SA0119 : Consider aliasing all table sources in the query

The topic describes the SA0119 analysis rule.

Message

Consider aliasing all table sources in the query

Description

The rule alerts for not aliased table sources in the FROM clause of SELECT, UPADTE and DELETE statements.

Consider aliasing all table sources in the query.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
NameDescriptionDefault Value
IgnoreSingleTableSources

The queries accessing single tables will be ignored.

yes

IgnoreTableValuesFunctions

The not aliased table valued functions will be ignored.

yes

IgnoreSystemObjects

The not aliased system tables or views will be ignored.

yes

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Design Rules

Additional Information

There is no additional info for this rule.

Example Test Script
SQL
Copy
 1SELECT * 
 2FROM Sales.Customer
 3INNER JOIN Sales.vStoreWithAddresses AS sa 
 4    ON CustomerID = sa.BusinessEntityID
 5WHERE TerritoryID = 5
 6
 7SELECT * 
 8FROM Sales.Customer /*IGNORE:SA0119*/
 9INNER JOIN Sales.vStoreWithAddresses AS sa 
10    ON CustomerID = sa.BusinessEntityID
11WHERE TerritoryID = 5
12
13SELECT *
14FROM HumanResources.Employee 
15UNION
16SELECT *
17FROM HumanResources.Employee 
18OPTION (MERGE UNION); 
19
20SELECT * FROM Sales.SalesOrderHeader WITH (NOLOCK)

Analysis Results

 MessageLineColumn
1SA0119 : Table source does not have an alias. Consider aliasing all table sources in the query.211
See Also

Other Resources

Analysis Rules