Analysis Rules

The current version of SQL Enlight comes with more than 70 static analysis rules based on best practices and coding guidelines for database development. The rules are grouped in four main categories - Naming, Design, Performance and Explicit Rules.

Naming

Naming issues arise if the name of a database object might cause unexpected problems or violate generally accepted conventions.

SA0014 : Avoid 'fn_' prefix when naming functions.
SA0015 : Avoid 'sp_' prefix when naming stored procedures.
SA0042 : Avoid using special characters in object names.
SA0043 : Avoid using reserved words for type names.

Design

Design issues include code that might not behave the way in which you expect, deprecated syntax, and issues that could cause problems when the design of your database changes.

SA0001 : Equality and inequality comparisons involving a NULL constant
SA0005 : Non-ANSI outer join syntax.
SA0006 : Non-ANSI inner join syntax.
SA0008 : Deprecated syntax 'string_alias' = expression.
SA0010 : Use TRY..CATCH construct or check the @@ERROR variable after executing a data manipulation statement (like INSERT/UPDATE/DELETE).
SA0011 : SELECT * in stored procedures, views and table valued functions.
SA0012 : Use SCOPE_IDENTITY() instead @@IDENTITY.
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.
SA0031 : Avoid using GOTO statement to improve readability.
SA0034 : Consider using parentheses to improve readability and avoid mistakes because of logical operator precedence.
SA0035 : TODO,HACK or UNDONE phrase found in a comment. It probably indicates a task that needs to be completed.
SA0036 : DELETE statement without WHERE clause.
SA0037 : UPDATE statement without WHERE clause.
SA0038 : The WHERE clause contains comparison which evaluates to TRUE.
SA0039 : The WHERE clause contains comparison which evaluates to FALSE.
SA0041 : Avoid joining with views.
SA0050 : Do not create clustered index on UNIQUEIDENTIFIER columns.
SA0051 : Possible result of Cartesian product due to incomplete table joins.
SA0052 : Avoid using undocumented and deprecated stored procedures.
SA0053 : Don't use deprecated TEXT,NTEXT and IMAGE data types.
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.
SA0059 : Check database for objects created with different than default or specified collation.

Performance

Performance issues include code database schema configuration that might noticeably reduce the speed in which database operations are completed. Many of these issues identify code that will cause a table scan when the code is executed.

SA0002 : Variable @variable declared but never used.
SA0003 : Variable @variable used but not previously assigned.
SA0004 : Variable @variable assigned but value never used.
SA0007 : Pattern starting with "%" in LIKE predicate.
SA0009 : Consider using a table variable instead a temporary table.
SA0013 : Avoid returning results in triggers.
SA0016 : Use of very small variable length type (size 1 or 2).
SA0017 : SET NOCOUNT ON option in stored procedures and triggers.
SA0023 : Avoid using inequality operators (<>,!=) in the WHERE clause.
SA0024 : Local cursor not closed.
SA0025 : Local cursor not explicitly deallocated.
SA0026 : Local cursor reference not explicitly deallocated.
SA0027 : Avoid wrapping filtering columns within a function in the WHERE clause.
SA0028 : Deterministic function calls can be extracted from the WHERE clause to avoid unnecessary table scan.
SA0029 : Input parameter never used.
SA0030 : Output parameter never assigned.
SA0032 : Avoid using NOT IN predicate in the WHERE clause.
SA0033 : Don't use the GROUP BY clause without an aggregate function.
SA0040 : Consider moving the column reference to one side of the comparison operator in order to use the column index.
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.
SA0049 : Table does not have a clustered index.
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.

Explicit Ruls

Special group of rules for gathering various information that could be useful during general development and code analysis.

EX0001 : Identify objects used in the script.
EX0002 : Get object dependencies - Objects on which database objects depend.
EX0003 : Get object dependencies - Objects that depend on other database objects.
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.
EX0008 : Generate CREATE INDEX statements for all columns included in foreign keys.
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 reindexing.
EX0014 : List the last execution status of all available SQL Server jobs.
EX0015 : Find Best Clustered Index
EX0016 : Generate DROP INDEX statements for inefficient indexes using dynamic management views information.
EX0017 : Generate ALTER INDEX statements for rebuilding or reorganizing fragmented indexes.
EX0018 : Analyze execution plan and check for high cost operations.