Query

APX1090 – Avoid Select(*)

Description:

This rule evaluates the T-SQL script for using “SELECT *”.

Avoid using SELECT * in your script unless it is required.

Always explicitly list the columns in a query to create more maintainable and predictable code.

For more information visit

https://stackoverflow.com/questions/321299/what-is-the-reason-not-to-use-select

Example script:

Message:

Statement contains SELECT *

APX1091 – TOP without ORDER BY

Description:

This rule evaluates the T-SQL script for using TOP in queries without a corresponding ORDER BY clause.

Although it will work, but it has no meaning to use TOP without ORDER BY clause, as requesting the TOP x rows implies that the data should be in a certain order.

For more information visit

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

Example script:

Message:

TOP clause without an ORDER BY clause

APX1093 – Non-ANSI join

Description:

This rule evaluates the T-SQL script for using Non-ANSI syntax for inner joins.

Use ANSI INNER JOIN instead.

For more information visit

https://stackoverflow.com/questions/1599050/will-ansi-join-vs-non-ansi-join-queries-perform-differently/1599201

Example script:

Message:

Statement uses Non-ANSI inner join(s)

APX1137 – Missing table aliases

Description:

This rule evaluates the T-SQL script for missing the specifying aliases for the participating tables with long names.

It is considered the good practice to alias long table names in the FROM clause and uses the alias’ to prefix columns referenced.

For more information visit

https://www.techonthenet.com/sql_server/alias.php

Example script:

Message:

Table reference, ‘*’Table, is missing a table/table alias prefix

APX1141 – Subquery join with TOP clause

Description:

This rule evaluates the T-SQL script for using TOP clauses for limiting the number of rules returned from a sub-query.

Using TOP clauses for limiting the number of rules returned from a sub-query can lead to missing results.

For more information visit

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

Example script:

Message:

Sub query join uses a TOP clause

APX1144 – Missing WHERE/JOIN clause

Description:

This rule evaluates the T-SQL script for NOT using WHERE or JOIN clause filter to limit affected rows.

Missing WHERE or JOIN clause filter, ALL data can be deleted or updated, which can result in inadvertent data loss/damage.

For more information visit

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

Example script:

Message:

UPDATE or DELETE Statement is missing WHERE and/or JOIN clause, creating a risk of data damage/loss

APX1170 – Column numbers in ORDER BY clause

Description:

This rule evaluates the T-SQL script for using the column number in the ORDER BY clause, that makes the code less transparent and maintainable.

Such code is brittle as it can be broken in changes to underlying, referenced objects.

Explicitly specify columns in an ORDER BY clause instead.

For more information visit

https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017

Example script:

Message:

ORDER BY clause, ‘*’, references columns by number

APX1176 – Missing join predicate

Description:

This rule evaluates the T-SQL script for missing a column on one or more sides of the join.

Missing a column on one or more sides of the join will result in a Cartesian product and performance degradation.

For more information visit

https://www.sqlshack.com/sql-join-overview-and-tutorial/

Example script:

Message:

Missing join predicate detected, which may result in excessive rows returned

APX1180 – SELECT INTO

Description:

This rule evaluates the T-SQL script for using SELECT INTO if you are concerned about execution plans.

Consider using this rule ONLY if you are concerned about execution plans.

Otherwise, SELECT INTO can be a benefit and improve performance.

For more information visit

https://www.sqlshack.com/sql-select-into-statement/

Example script:

Message:

SELECT INTO detected

APX1184 – ISNUMERIC function

Description:

This rule evaluates the T-SQL script for the ISNUMERIC function.

Consider using LIKE or PATINDEX instead.

For more information visit

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

Example script:

Message:

ISNUMERIC function detected

APX1185 – CHARINDEX in Where clause

Description:

This rule evaluates the T-SQL script for using CHARINDEX in a WHERE clause.

Using CHARINDEX in a WHERE clause can degrade performance.

Consider using LIKE instead.

For more information visit

https://www.mssqltips.com/sqlservertip/1236/avoid-sql-server-functions-in-the-where-clause-for-performance/

and

https://www.sqlservercentral.com/Forums/Topic1428768-391-1.aspx

Example script:

Message:

Used objects contains CHARINDEX function in a WHERE clause

APX1193 – IN predicate

Description:

This rule evaluates the T-SQL script for using (NOT) IN predicate, as it may perform significantly worse than EXISTS (NOT EXISTS), especially for nullable fields.

Consider using (NOT EXISTS) instead, that can perform faster than (NOT) IN.

For more information visit

https://www.sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

and

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

Example script:

Message:

DML statement sub query contains (NOT)IN operator. Consider (NOT)EXISTS

APX1197 – OR operator in WHERE clause

Description:

This rule evaluates the T-SQL script for using OR operator in WHERE clauses.

Using OR operator in WHERE clauses can inhibit the ability of indexes to improve performance and prevent the creation of an optimal query plan.

For more information visit

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/or-transact-sql?view=sql-server-2017

Example script:

Message:

OR operator in WHERE clause detected

APX1210 – Missing columns in insert

Description:

This rule evaluates the T-SQL script for missing explicit specification of target column list in the INSERT statements.

Missing explicit specification of target column list in the INSERT statements can result in incorrect insert operation.

Consider specifying the columns in the correct order within the INSERT INTO statement.

For more information visit

https://www.sqlshack.com/overview-of-the-sql-insert-statement/

Example script:

Message:

Insert statement has no column list

APX1230 – SET ROWCOUNT

Description:

This rule evaluates the T-SQL script for using SET ROWCOUNT.

Use TOP option instead because it is more predictable.

SET ROWCOUNT limits ALL eligible queries. That means Triggers also.

For more information visit

https://sqlstudies.com/2013/10/07/use-top-instead-of-set-rowcount

Also, SET ROWCOUNT is bad on many levels.

For more information visit:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-rowcount-transact-sql

Example script:

Message:

Statement uses SET ROWCOUNT

APX1243 – MERGE

Description:

This rule evaluates the T-SQL script for using MERGE statement.

MERGE statement has some flaws in its usage and can be problematic for the unwary.

Use INSERT\UPDATE statements instead.

For more information visit

https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

Example script:

Message:

Batch contains MERGE keyword

APX1276 – Statement without TRY…CATCH

Description:

This rule evaluates the T-SQL script for NOT encapsulating the SELECT INTO, INSERT, DELETE and UPDATE statements within a TRY…CATCH block.

Consider encapsulating the SELECT INTO, INSERT, DELETE and UPDATE statements within a TRY…CATCH block to guarantee a safe-fail for the script.

For more information visit

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql

Example script:

Message:

Insert/update/delete/select into statement is not encapsulated in TRY…CATCH block

APX1286 – Database context change

Description:

This rule evaluates the T-SQL script for changing the database context mid-batch with a USE [database] statement, that is a potential indicator of problematic code and should be at least reviewed.

If OK, this rule/object combination can be explicitly ignored.

Example script:

Message:

Batch contains USE statement

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)