Analysis template updated with 9 new analysis rules

We have just released 9 brand new analysis rules for SQL Enlight.

Here is a list and a short overview of the rules:

• SA0050 – Do not create clustered index on UNIQUEIDENTIFIER columns.
  Consider moving the clustered index to a different column or consider using NewSequentialId() system function for generating sequential unique identifiers. The native uniqueidentifier data type is not suitable for clustered indexing, because causes terrible page splits because its value is completely random.
•  SA0051 – Possible result of Cartesian product due to incomplete table joins.
  The rule checks the T-SQL code for possible unintended result of Cartesian product because of incomplete table join.
  Cross products are also known as Cartesian products are most commonly caused by missing join condition for any of the joined tables in the ON or the WHERE clauses. To correct this issue, ensure that your queries are correctly formed, that there are predicate for all joined tables.
  The rule identifies the joined table sources which are not referenced neither in the join conditions nor in the WHERE clause.
• SA0052 – Avoid using undocumented and deprecated stored procedures.
  The rule checks the T-SQL code for calls to deprecated or undocumented sotored procedures.
  Deprecated procedures can be removed in a future version of Microsoft SQL Server while undocumented ones can be removed or changed even in the next release of update or service pack.Avoid using this procedures in new development work, and plan to modify applications that currently use them.
• SA0053 – Don’t use deprecated TEXT,NTEXT and IMAGE data types.
  The data types ntext, text, and image will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.
• SA0054 – Avoid modification of parameters in a stored procedure prior to use in a query.
  For best query performance, in some situations you’ll need to avoid assigning a new value to a parameter of a stored procedure within the procedure body, and then using the parameter value in a query. The stored procedure and all queries in it are initially compiled with the parameter value first passed in as a parameter to the query.
• SA0055 – Consider indexing the columns referenced by IN predicates in order to avoid table scans.
  The rule checks for IN predicates that reference non indexed columns.
  Using columns which do not have index can cause a performance reducing table scan.
  The following changes will help to avoid this issue:
  – Add an index to the column referenced by the IN predicate.
  – Change the IN predicate to reference only indexed columns.
• SA0056 – Index has exact duplicate or overlapping  index.
  The rule matches exact duplicating or partially duplicating indexes.
  The exact duplicating indexes must have the same key columns in the same order, and the same included columns but in any order. These indexes are sure targets for elimination.
  The overlapping indexes share the same leading key columns, but the included columns are ignored. These types of indexes are probable dead indexes walking.
• EX0006 – Identify possible missing Foreign Keys.
  The rule checks database tables for columns which do not have a foreign key reference, but match by name and data type to a primary key of a different table.The rule checks only for single columns and assumes that the referencing columns has the same name as the primary key column name.
• EX0007 – List all DML and DDL triggers in current database.
  The rule lists all triggers in the current database.
• EX0008 – Generate CREATE INDEX statements for all columns included in foreign keys.
  The rule checks for not indexed foreign keys in the current database and generated the appropriate CREATE INDEX statements.

The rules and the updated analysis template can be  download here.