Performance

APX1092 – HAVING with non-aggregate functions

Description:

This rule evaluates the T-SQL script for using HAVING with non-aggregate functions.

HAVING should be used only with aggregate functions.

For more information visit

https://www.techonthenet.com/sql/having.php

Example script:

Message:

HAVING clause is used in a non aggregate function

APX1096 – Comparison with NULL

Description:

This rule evaluates the T-SQL script for comparing with NULL values, as it is not ANSI-compliant.

Use IS NULL or IS NOT NULL instead.

For more information visit

https://www.w3schools.com/sql/sql_null_values.asp

Example script:

Message:

Comparison operator(s) used with NULLs. Use IS (NOT) NULL instead

APX1120 – Temporary table

Description:

This rule evaluates the T-SQL script for using temporary tables.

Table variables are preferred in certain cases (e.g. small result set) to temporary tables when the scope is a single batch.

Avoid excessive use of the temporary tables without a real need.

For more information visit

https://www.sqlshack.com/when-to-use-temporary-tables-vs-table-variables/

Example script:

Message:

Consider replacing temporary table, #’*’Table, with a table variable

APX1130 – Not equal operator in WHERE

Description:

This rule evaluates the T-SQL script for using Not Equal operators (<>, !=) in the WHERE clause.

Using Not Equal operators (<>, !=) in the WHERE clause may result in the table and index scans which can degrade performance.

Avoid using Not Equal operators (<>, !=) in the WHERE clause as possible.

For more information visit

https://www.mssqltips.com/sqlservertutorial/3203/avoid-using-not-equal-in-where-clause/

Example script:

Message:

Non-equal operator(s) (<>, !=) detected in the WHERE clause

APX1135 – DML and DDL mixed

Description:

This rule evaluates the T-SQL script for mixing between DML and DDL statement, as DDL statements may result in recompiles.

Place DDL statements first for optimal performance.

For more information visit

http://robertmarkbramprogrammer.blogspot.com/2011/09/separate-ddl-and-dml-in-transact-sql.html

Example script:

Message:

DDL statement detected after the DML statement

APX1145 – “%” first in LIKE

Description:

This rule evaluates the T-SQL script for using Wildcard Characters to Start Search Criteria.

Using Wildcard Characters to Start Search CriteriaI t will negate the benefit of any indexes and degrade performance.

For more information visit

https://www.mssqltips.com/sqlservertutorial/3205/avoid-using-wildcard-characters-to-start-search-criteria/

Example script:

Message:

LIKE predicate starts with the ‘%’ character

APX1173 – NOT IN in a WHERE clause

Description:

This rule evaluates the T-SQL script for using NOT IN predicate in the WHERE clause.

Using NOT IN predicate in the WHERE clause may degrade performance.

For more information visit

https://www.sqlshack.com/t-sql-commands-performance-comparison-not-vs-not-exists-vs-left-join-vs-except/

Example script:

Message:

NOT IN predicate detected in the WHERE clause

APX1186 – Cursor

Description:

This rule evaluates the T-SQL script for using cursor widely.

Use the set operations instead, as it is preferred over cursors for performance reasons.

For more information visit

https://docs.microsoft.com/en-us/sql/relational-databases/cursors?view=sql-server-2017

Example script:

Message:

Cursor detected

APX1225 – Missing clustered index

Description:

This rule evaluates the T-SQL script for NOT creating a clustered index in your tables.

Most tables should have a clustered index, with a few exceptions, for performance enhancement purposes.

For more information visit

https://www.sqlshack.com/designing-effective-sql-server-clustered-indexes/

Example script:

Message:

Table is missing clustered index

APX1234 – WITH RECOMPILE

Description:

This rule evaluates the T-SQL script for using the WITH RECOMPILE query hint.

Use RECOMPILE option instead as it may improve performance.

For more information visit

https://www.sqlservercentral.com/Forums/Topic1755419-3387-1.aspx

Example script:

Message:

WITH RECOMPILE clause detected. Consider RECOMPILE to improve performance

APX1273 – Stored procedure calls from a trigger

Description:

This rule evaluates the T-SQL script for calling a stored procedure from a trigger.

Avoid calling a stored procedure from a trigger that can be a major performance issue.

For more information visit

https://www.sqlshack.com/triggers-in-sql-server/

Example script:

Message:

Procedure call from trigger detected

APX1274 – Transactions in a stored procedure

Description:

This rule evaluates the T-SQL script for using transactions in the stored procedure.

Avoid using transactions in the stored procedure as attempting to do a rollback within a stored procedure can be problematic.

For more information visit

https://blog.sqlauthority.com/2010/06/02/sql-server-stored-procedure-and-transactions/

Example script:

Message:

Rollback detected within procedure

APX1277 – Do not use SET FORCEPLAN OFF

Description:

This rule evaluates the T-SQL script for using SET FORCEPLAN widely, as it should be used only be experienced developers/DBAs in very specific circumstances.

SET FORCEPLAN essentially overrides the logic used by the query optimizer to process a Transact-SQL SELECT statement.

The data returned by the SELECT statement is the same regardless of this setting.

The only difference is the way in which SQL Server processes the tables to satisfy the query.

For more information visit

https://technet.microsoft.com/en-us/library/ms188344(v=sql.110).aspx

Example script:

Message:

SET FORCEPLAN OFF detected

APX1278 – Incorrect usage of COUNT()

Description:

This rule evaluates the T-SQL script for using COUNT().

Using COUNT()is not recommended.

Use EXISTS() instead.

For more information visit

https://docs.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql?view=sql-server-2017

Example script:

Message:

Statement contains COUNT(*) function call. Consider EXISTS() instead

APX1280 – Cursor not declared as readonly

Description:

This rule evaluates the T-SQL script for NOT declaring the cursor as read-only when there is no UPDATE/DELETE operation.

Consider declaring the cursor as read-only when there is no UPDATE/DELETE operation.

For more information visit

https://www.sqlshack.com/sql-server-cursor-attributes/

Example script:

Message:

Non-actionable Cursor is not declared as readonly

SQLShack

SQLShack

SQL Shack was created as a community service from ApexSQL, with the goal to share SQL Server knowledge through articles written by SQL Server professionals and community members
SQLShack

Latest posts by SQLShack (see all)