Performance
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.
Rule: SA0002
Message
Variable @variable declared but never used.
Description
This rule checks for declared neither assigned nor used variables.
Category
Additional Information
None
Rule: SA0003
Message
Variable @variable used but not previously assigned.
Description
This rule checks for use of not initialized variables.
Category
Additional Information
None
Rule: SA0004
Message
Variable @variable assigned but value never used.
Description
This rule checks for values assigned to a variable that are either not used or overwriten by new value.
Category
Additional Information
None
Rule: SA0007
Message
Pattern starting with "%" in LIKE predicate.
Description
This rule checks for for usage of wildcard characters at the beginning of a word while searching using the LIKE keyword.
Usage of wildcard characters at the beggining of a LIKE pattern results in an index scan, which defeats the purpose of an index.
The following two statement results in an index scan, while the third statement results in an index seek:
SELECT LocationID
FROM Locations
WHERE Specialities LIKE '%pples'
SELECT LocationID
FROM Locations
WHERE Specialities LIKE 'A%s'
SELECT LocationID
FROM Locations
WHERE Specialities LIKE 'Ap%'
Category
Additional Information
None
Rule: SA0009
Message
Consider using a table variable instead a temporary table.
Description
This rule checks for usages of temporary tables that may be replaced by use of table variables. A temporary table that does not have CREATE INDEX is issued on it and is created and dropped in the same batch may be replaced by table variable.
Note that if large data volumes will be inserted in the temporary table it may still be preferred to use temporary tables due to parallel execution restrictions and statistics maintenance.
Example:
CREATE TABLE ##mail
(
toAddress NVARCHAR( 100 ) ,
fromAddres NVARCHAR( 100 ) ,
subject NVARCHAR( 256 ) ,
body NVARCHAR( 4000 )
);
INSERT INTO ##mail( toAddress ,
fromAddres ,
subject ,
body )
VALUES( 'jhonnybravo@cartoonnetwork.com' ,
'afan@gmail.com' ,
'Hi' ,
'Put your subject here.' );
SELECT toAddress ,
fromAddres ,
subject ,
body
FROM ##mail
DROP TABLE ##mail;
The above temporary table can be replaced with:
DECLARE @mail AS TABLE( toAddress NVARCHAR( 100 ) ,
fromAddres NVARCHAR( 100 ) ,
subject NVARCHAR( 256 ) ,
body NVARCHAR( 4000 ) );
INSERT INTO @mail( toAddress ,
fromAddres ,
subject ,
body )
VALUES( 'jhonnybravo@cartoonnetwork.com' ,
'afan@gmail.com' ,
'Hi' ,
'Put your
subject here.' );
SELECT toAddress ,
fromAddres ,
subject ,
body
FROM @mail;
Category
Additional Information
None
Rule: SA0013
Message
Avoid returning results in triggers.
Description
This rule scans triggers to ensure they do not send data back to the caller.
Applications that modify tables or views with triggers do not necessarily expect results to be returned as part of the modification operation. For this reason it is not recommended to return results from within triggers.
This rule flags as not recommended the use of the following statements within a trigger:
PRINT statement
SELECT (without assignment or INTO clause)
FETCH (without assignment)
Category
Additional Information
None
Rule: SA0016
Message
Use of very small variable length type (size 1 or 2).
Description
This rule checks for use of very small variable length type (ith size of 1 or 2).
It is recommended to use fixed length data type when size is less than 3. Variable length type with size less than 3 consumes more space than the same fixed length type.
Category
Additional Information
None
Rule: SA0017
Message
SET NOCOUNT ON option in stored procedures and triggers.
Description
This rule scans triggers, stored procedures and functions to ensure they SET NOCOUNT to ON at the beginning.
Use SET NOCOUNT ON at the beginning of your SQL batches, stored procedures and triggers in production environments, as this prevents the sending of DONE_IN_PROC messages and suppresses messages like '(1 row(s) affected)' to the client for each statement in a stored procedure.
For stored procedures that contain several statements that do not return much actual data, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.
Category
Additional Information
None
Rule: SA0023
Message
Avoid using inequality operators (<>,!=) in the WHERE clause.
Description
The rule checks for usage of the not equal operator in the WHERE clause as it result table and index scans. Consider replacing the not equal operator with equals (=) or inequaluity operators (>,>=,<,<=) if possible.
For example:
SELECT tleft.c1
FROM tleft
RIGHT JOIN tright
ON tleft.c1 = tright.c1
WHERE tright.c1 <> 2;
You can codify the query to use the column index and still return the same result:
SELECT tleft.c1
FROM tleft
RIGHT JOIN tright
ON tleft.c1 = tright.c1
WHERE tright.c1 < 2
AND tright.c1 > 2;
Category
Additional Information
None
Rule: SA0024
Message
Local cursor not closed.
Description
The rule checks if any local cursor is closed until the end of the batch. Because when open, the cursor still holds locks on referred-to-tables or views, you should explicitly close it as soon as it is no longer needed.
Category
Additional Information
CLOSE (Transact-SQL)
OPEN (Transact-SQL)
Rule: SA0025
Message
Local cursor not explicitly deallocated.
Description
The rule checks if cursor name is deallocated before the end of the batch using the DEALLOCATE statement.
Category
Additional Information
Rule: SA0026
Message
Local cursor reference not explicitly deallocated.
Description
The rule checks if cursor variable is deallocated before the end of the batch using the DEALLOCATE statement.
Category
Additional Information
Rule: SA0027
Message
Avoid wraping filtering columns within a function in the WHERE clause.
Description
When a filtering WHERE clause column is wrapped inside a function, the query optimizer does not see the column and if an index exists on the column, the index most likely will not to be used.
Example 1:
SELECT *
FROM users
WHERE substring( firstname ,
1 ,
1 ) = 'm'
Example 2:
SELECT *
FROM users
WHERE firstname LIKE 'm%'
The both statements return the same result, but the first will be slower,because it won't use the column.
Category
Additional Information
None
Rule: SA0028
Message
Deterministic function calls can be extracted from the WHERE clause to avoid unnecessary table scan.
Description
Consider extracting function calls made in the WHERE clause into local variable, if the function result does not depend on table columns and if it can be evaluated before executing the query. This is necessary, because such function calls can sometimes make the SQL optimizer to perform unnecessary table scan.
Example 1:
SELECT *
FROM Production.Product
WHERE CHECKSUM( N'Bearing Ball' ) = cs_Pname
AND Name = N'Bearing Ball';
Example 2:
DECLARE @Checksum AS INT
SET @Checksum = CHECKSUM( N'Bearing Ball' )
SELECT *
FROM Production.Product
WHERE @Checksum = cs_Pname
AND Name = N'Bearing Ball';
The statement in the first exmaple is more likely to perform table scan than the second example.
Category
Additional Information
None
Rule: SA0029
Message
Input parameter never used.
Description
This rule checks for not used stored procedure or function input paremeters. Unused parameters not necessarily negatively affect the performance, but they just add bloat to your stored procedures and functions.
Category
Additional Information
None
Rule: SA0030
Message
Output parameter never assigned.
Description
The rule checks for not used stored procedure output parameters. Unused parameters not necessarily negatively affect the performance, but they just add bloat to your stored procedures and functions.
Category
Additional Information
None
Rule: SA0032
Message
Avoid using NOT IN predicate in the WHERE clause.
Description
Using NOT IN predicate in the WHERE clause generally performs badly, because the SQL Server optimizer has to use a TABLE SCAN instead of an INDEX SEEK even the filtering columns are covered by index. Consider using one of the following options instead all of which offer better performance:
- EXISTS or NOT EXISTS
- IN
- Perform LEFT OUTER JOIN and check for a NULL
SELECT FirstName ,
LastName
FROM Person.Contact AS c
JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE EmployeeID NOT IN( SELECT SalesPersonID
FROM Sales.SalesPerson
WHERE SalesQuota > 250000 )
GO
-- The above statement can be replaced with this one:
SELECT FirstName ,
LastName
FROM Person.Contact AS c
JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE EmployeeID IN( SELECT SalesPersonID
FROM Sales.SalesPerson
WHERE SalesQuota <= 250000 )
Category
Additional Information
None
Rule: SA0033
Message
Don't use the GROUP BY clause without an aggregate function.
Description
Consider using DISTINCT option instead of using the GROUP BY clause without using aggregate function, because the DISTINCT option will be faster and will give the same end result.
-- Example 1:
SELECT ColumnA ,
ColumnB
FROM T
GROUP BY ColumnA ,
ColumnB
-- Example 2:
SELECT DISTINCT
ColumnA ,
ColumnB
FROM T
Statement in Example 2 will be faster and will return exactly the same result.
Category
Additional Information
None
Rule: SA0040
Message
Consider moving the column reference to one side of the comparison operator in order to use the column index.
Description
The rule checks for constant to column comparison expressions inside search conditions.
Try to rewrite the comparison expression and move the column on the one side of the comparison operator. This will allow the query optimizer to take advantage of the column index (assuming one is available) instead of performing table scan.
Category
Additional Information
None
Rule: SA0044
Message
Consider creating indexes on all columns included in foreign keys.
Description
The rule checks for not indexed foreign keys in the current database.
Create an index on any foreign key as the foreign keys are used in joins almost always benefit from having an index.
It is better to create indexes on all foreign keys, despite the possible overhead of maintaining unneeded indexes than not to have index when needed.
Category
Additional Information
None
Rule: SA0045
Message
Consider updating statistics as they appear outdated and may mislead the query optimizer.
Description
The rule checks the statistics on all tables and indexed views in the current database for outdated statistics.
Evaluation of the statistics can be based on the number of modified rows or on the last time the statistics were updated.
The rules used to evaluate the statistics using the number of modified rows are the same as the rules SQL Server uses to update statistics automatically.
These rules are as follows:
- If a table has 6 or fewer rows, statistics will be updated after 6 changes
- If a table has 500 or fewer rows, statistics will be updated after 500 changes
- If a table has more than 500 rows, statistics will be updated after 20% of the total rows plus 500 rows are modified,added or deleted
Category
Additional Information
None
Rule: SA0046
Message
Consider creating statistics on all composite index columns.
Description
SQL Server creates statistics only for the first key column of a composite index when the index is created.
The rule checks for all composite index key columns other than the first one, which do not have statistics stored.
Category
Additional Information
None
Rule: SA0047
Message
Consider indexing the column as it is used in a WHERE clause or JOIN condition.
Description
The rules checks WHERE or JOIN clauses for non-indexed columns staying on the one side of comparison expression. Indexing those columns may improve the query performance.
Category
Additional Information
None
Rule: SA0048
Message
Table does not have a primary key.
Description
The rule checks all the tables in the current database for having a primary key defined.
Category
Additional Information
None
Rule: SA0049
Message
Table does not have a clustered index.
Description
The rule checks all the tables in the current database for having a clustered index defined.
Category
Additional Information
None
Rule: SA0054
Message
Avoid modification of parameters in a stored procedure prior to use in a query.
Description
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.
Category
Additional Information
None
Rule: SA0055
Message
Consider indexing the columns referenced by IN predicates in order to avoid table scans.
Description
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.
Category
Additional Information
None