Design
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.
Rule: SA0001
Message
Equality and inequality comparisons involving a NULL constant found. Use IS NULL or IS NOT NULL.
Description
This rule scans stored procedures, views, functions and triggers to flag use of equality and inequality comparisons involving a NULL constant. These comparisons are undefined when ANSI_NULLS option is set to ON.
It is recommended to set ANSI_NULLS to ON and use the IS keyword to compare against NULL constants.
Category
Additional Information
None
Rule: SA0005
Message
Non-ANSI outer join syntax.
Description
This rule checks for the use of non-ANSI outer joins (*= and =* syntax). It is recommended that outer joins use the ANSI specified syntax, for example:
SELECT tab1.c1 ,
tab3.c2
FROM tab1
LEFT OUTER JOIN tab2
ON tab1.c3 = tab2.c3
RIGHT JOIN tab3
LEFT OUTER JOIN tab4
ON tab3.c1 = tab4.c1
ON tab2.c3 = tab4.c3
Category
Additional Information
None
Rule: SA0006
Message
Non-ANSI inner join syntax.
Description
This rule checks for the use of non-ANSI inner join syntax.
It is recommended to use the more readable ANSI-Standard JOIN clauses instead of the old style joins. The WHERE clause is used only for filtering data with the ANSI joins, but with older style joins, the WHERE clause handles both the join condition and filtering data.
Old style join syntax:
SELECT a.au_id ,
t.title
FROM titles AS t ,
authors AS a ,
titleauthor AS ta
WHERE a.au_id = ta.au_id
AND ta.title_id = t.title_id
AND t.title LIKE 'Example%'
ANSI join syntax:
SELECT a.au_id ,
t.title
FROM authors AS a
INNER JOIN titleauthor AS ta
ON a.au_id = ta.au_id
INNER JOIN titles AS t
ON ta.title_id = t.title_id
WHERE t.title LIKE 'Example%'
Category
Additional Information
None
Rule: SA0008
Message
Deprecated syntax 'string_alias' = expression.
Description
This rule checks T-SQL script for use of column aliasing where the name of the expression uses a string value. The syntax is deprecated and it is recommended to use quoted identifiers instead.
The following syntax is deprecated:
SELECT 'alias_for_col' = au_id + au_id
FROM dbo.authors
Recommended alternatives are:
expression [AS] column_alias
expression [AS] [column_alias]
expression [AS] "column_alias"
expression [AS] 'column_alias'
column_alias = expression
Category
Additional Information
None
Rule: SA0010
Message
Use TRY..CATCH construct or check the @@ERROR variable after executing a data manipulation statement (like INSERT/UPDATE/DELETE).
Description
This rule checks if the global variable @@ERROR is examined immediately after executing a data manipulation statement (like INSERT/UPDATE/DELETE) or the statement is in TRY_CATCH block.
This is important, because, by default, SQL Server will not rollback all the previous changes within a transaction if a particular statement fails and setting XACT_ABORT is not ON.
Category
Additional Information
None
Rule: SA0011
Message
SELECT * in stored procedures, views and table valued functions.
Description
This rule checks stored procedures, functions, views and triggers for use of '*' in column lists of SELECT statements. Though use of '*' is convenient, it may lead to less maintainable applications. Changes to table or view definitions may cause errors or performance decrease. Using the proper column names takes less load on the database, decreases network traffic and hence can greatly improve performance.
As an example, the following syntax is not recommended:
SELECT *
FROM dbo.Books
It is recommended to have an explicit list whenever the column to retrieve is known to the application.
SELECT b.b_id ,
b.b_title
FROM dbo.Books AS b
Category
Additional Information
None
Rule: SA0012
Message
Use SCOPE_IDENTITY() instead @@IDENTITY.
Description
The rule checks for use of @@IDENTITY server variable. It is recommended to use SCOPE_IDENTITY() instead. @@IDENTITY is not limited to a specific scope and is not a reliable indicator of the most recent user-created identity if the column is part of a replication article.
Category
Additional Information
Rule: SA0018
Message
Support for constants in ORDER BY clause have been deprecated.
Description
This rule checks for use of ORDER BY clause specifying constants as sort columns. Support for this syntax has been deprecated.
As an example, the following syntax is not recommended:
SELECT au_id
FROM dbo.authors
ORDER BY 'a' ,
NULL
Category
Additional Information
None
Rule: SA0019
Message
TOP clause used in a query without an ORDER BY clause.
Description
This rule checks for usages of TOP in queries without an ORDER BY clause.
It is generally recommended to specify sort criteria when using TOP clause. Otherwise, the results produced will be plan dependent and may lead to undesired behavior.
Category
Additional Information
None
Rule: SA0020
Message
Always use a column list in INSERT statements.
Description
This rule checks objects for use of INSERT statements without explicit specification of target column list.
When inserting into a table or view, it is recommended that the target column_list be explicitly specified. This results in more maintainable code and helps in avoiding problems when the table structure changes (like adding or dropping a column).
Category
Additional Information
None
Rule: SA0021
Message
Deprecated usage of table hints without WITH keyword.
Description
This rule checks stored for use of table hints without the WITH keyword.
It is recommended that hints be specified using the WITH keyword.SQL Server 2005 does not support the use of more than one hints in a T-SQL statement unless the WITH keyword is specified.
As an example, the following syntax is not supported in SQL Server 2005:
SELECT au_id
FROM dbo.authors ( UPDLOCK ,
PAGLOCK )
The recommended approach is to use:
SELECT au_id
FROM dbo.authors WITH( UPDLOCK ,
PAGLOCK )
Queries specifying a single hint will work in SQL Server 2005, though it is still recommended to use the WITH keyword.
Category
Additional Information
None
Rule: SA0022
Message
Index type (CLUSTERED or NONCLUSTERED) not specified.
Description
This rule checks that creation of indexes is done with explicit specification of index type.
It is recommended that the CLUSTERED or NONCLUSTERED specifications be used (as appropriate) when creating an index.
Category
Additional Information
None
Rule: SA0031
Message
Avoid using GOTO statement to improve readability.
Description
Use of the GOTO statement is generally considered to be poor programming practice and is not recommended.
Extensive use of GOTO tends to lead to unreadable code especially when procedures grow long. In most of the cases use of the GOTO statement is not necessary because there are better statements available to control the execution path. There are no specific situations that require the use of GOTO; instead it is more often used for convenience.
Category
Additional Information
None
Rule: SA0034
Message
Consider using parentheses to improve readability and avoid mistakes because of logical operator precedence.
Description
When more than one logical operator is used in a statement, NOT is evaluated first, then AND, and finally OR. Arithmetic (and bitwise) operators are handled before logical operators.
The use of parentheses, even when not required, can improve the readability of queries and reduce the chance of making a subtle mistake because of operator precedence. There is no significant performance penalty in using parentheses.
Consider this example statement:
SELECT title_id ,
type ,
price
FROM pubs.dbo.titles
WHERE type = 'physics'
OR type = 'architecture'
AND advance > 30
In this example, the advance condition pertains to architecture books and not to physics books because AND has precedence over OR. This example is more readable than the original example, although they are syntactically the same:
SELECT title_id ,
type ,
price
FROM pubs.dbo.titles
WHERE type = 'physics'
OR ( type = 'architecture'
AND price > 30 )
You can change the meaning of the query by adding parentheses to force evaluation of the OR first. This query finds all physics and architecture books that have price over $30:
SELECT title_id ,
type ,
price
FROM titles
WHERE ( type = 'physics'
OR type = 'architecture' )
AND advance > 30
Category
Additional Information
http://msdn.microsoft.com/en-us/library/ms186992.aspx
Rule: SA0035
Message
TODO,HACK or UNDONE phrase found in a comment. It probably indicates a task that needs to be completed.
Description
This rule checks comments for one of TODO,HACK and UNDONE phrases. Consider checking this comments and completing the unfinished tasks.
Category
Additional Information
None
Rule: SA0036
Message
DELETE statement without WHERE clause.
Description
The rule looks for DELETE statements not having WHERE clause.
Consider reviewing your code to avoid unintentionally losing all the rows in the table.
Category
Additional Information
None
Rule: SA0037
Message
UPDATE statement without WHERE clause.
Description
The rule looks for UPDATE statements not having WHERE clause.
Consider reviewing your code to avoid unintentionally updating all the records in the table.
Category
Additional Information
None
Rule: SA0038
Message
The WHERE clause contains comparison which evaluates to TRUE.
Description
The rule checks for WHERE clauses which contain comparison expression that will always evaluate to TRUE.
Category
Additional Information
None
Rule: SA0039
Message
The WHERE clause contains comparison which evaluates to FALSE.
Description
The rule checks for WHERE clauses which contain comparison expression that will always evaluate to FALSE.
Category
Additional Information
None
Rule: SA0041
Message
Avoid joining with views.
Description
The rule checks for joining with views as this may have performance implication when used without having good knowledge of the underlying tables and may lead to unnecessary joins.
Although views are useful for many reasons, they hide the underlying sources and may mislead unacquainted developers and produce redundant joins.
Category
Additional Information
None
Rule: SA0050
Message
Do not create clustered index on UNIQUEIDENTIFIER columns.
Description
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.
Category
Additional Information
None
Rule: SA0051
Message
Possible result of Cartesian product due to incomplete table joins.
Description
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.
Category
Additional Information
None
Rule: SA0052
Message
Avoid using undocumented and deprecated stored procedures.
Description
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.
For more information, check the deprecated features list:
Microsoft SQL Server 2005
http://msdn.microsoft.com/en-us/library/ms143729(SQL.90).aspx
Microsoft SQL Server 2008
http://msdn.microsoft.com/en-us/library/ms143729.aspx
Category
Additional Information
None
Rule: SA0053
Message
Don't use deprecated TEXT,NTEXT and IMAGE data types.
Description
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.
Category
Additional Information
None
Rule: SA0056
Message
Index has exact duplicate or overlapping index.
Description
The rule mathces 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.
Considerations:
- Before dropping the duplicaing indexes,check for index hints referencing the particualr duplicating index.
- Be careful when dropping a partial duplicate index if the two indexes differ greatly in width.Consider setting appropriate value to the MaximumNonOverlappingKeyColumns in order the rule to
filter only the indexes having small differenece in the number of indexed columns.
For example:
If Index1 is a very wide index with 12 columns, and Index2 is a narrow two-column index that shares the first two columns,
you may want to leave Index2 as a faster, compact, narrower index.
Parameters
|
Name |
Description |
|---|---|
| MaximumNonOverlappingKeyColumns | The parameter determines when to ignore partially duplicating indexes when they have too much different columns. |
Category
Additional Information
None
Rule: SA0057
Message
Consider using EXISTS predicate instead of IN predicate.
Description
The rule check T-SQL code for IN predicate using a sub-query as they can be replaced by EXISTS prediacate.
Using EXISTS predicate is often considered better than IN predicate, especially when NOT IN predicate is used.
Parameters
|
Name |
Description |
|---|---|
| CheckForNotInOnly | The parameter specifies if to check only for NOT IN predicate. |
Category
Additional Information
None
Rule: SA0058
Message
Avoid converting dates to string during date comparison.
Description
The rule checks T-SQL script for date comparison made with using conversion to string (the CONVERT function).
Consider using DATEADD and DATEDIFF functions as the to string conversion can lead to incorrect results.
Category
Additional Information
None
Rule: SA0059
Message
Check database for objects created with different than default or specified collation or for usage of collation different than the database default or the specified collation.
Description
The rule checks database ad T-SQL code for objects uaing with different than the the database default or the specified collation.
Parameters
|
Name |
Description |
|---|---|
| Collation | Specific collation to check for. |
Category
Additional Information
None