SQL code smells rules

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:

SELECT * FROM FirstTable;

SELECT a, b, c FROM SecondTable;

UPDATE ThirdTable SET a = 0;

SELECT * FROM FourthTable;

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:

SELECT TOP 10 *

FROM FirstTable;

SELECT TOP 100 a,

FROM SecondTable;

SELECT TOP 100 x,

FROM SixthTable;

UPDATE ThirdTable

SET

a = 0;

UPDATE SeventhTable

SET

b = 0;

SELECT TOP 100 *

FROM FourthTable

ORDER BY id DESC;

SELECT TOP 100 *

FROM FifthTable

ORDER BY [name 1];

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:

SELECT a.name,

a.id,

b.value

FROM a,

WHERE a.id = b.ref_id;

SELECT a.name,

a.id,

b.value

FROM FirstTable AS a,

SecondTable AS b

WHERE a.id = b.ref_id;

SELECT a.name,

a.id,

b.value

FROM ThirdTable AS a

INNER JOIN FourthTable AS b ON a.id = b.ref_id;

SELECT a.name,

a.id,

b.value

FROM FifthTable AS a

INNER JOIN SixthTable AS b ON a.id = b.ref_id

WHERE b.value > 0;

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:

SELECT *

FROM FirstTable;

SELECT *

FROM SecondTable,

ThirdTable;

SELECT *

FROM FourthTable

INNER JOIN FifthTable ON id1 = id2;

SELECT *

FROM SixthTable a;

SELECT *

FROM SeventhTable AS a;

SELECT *

FROM EighthTable a,

NinethTable b;

SELECT *

FROM TenthTable a

INNER JOIN EleventhTable b ON a.id = b.id;

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:

SELECT Col1,

Col2,

(

SELECT MAX(Price)

FROM PriceTable AS b

WHERE a.ID = b.GoodID

) AS MaxPrice

FROM Goods AS a;

SELECT Name

FROM Persons

WHERE Town =

(

SELECT Town

FROM Towns

WHERE [Index] = 55555

);

SELECT Name

FROM Persons

WHERE Town IN

(

SELECT TOP 5 Town

FROM Towns

WHERE [Region] = N'Boston'

);

SELECT Name

FROM Persons

WHERE Town IN

(

SELECT TOP 5 Town

FROM Towns

WHERE [Region] = N'Boston'

)

AND Age IN

(

SELECT TOP 7 Age

FROM Persons

ORDER BY Sallary DESC

);

SELECT Name

FROM Persons

WHERE Town IN

(

SELECT TOP 5 Town

FROM Towns

WHERE [Region] = N'Boston'

)

AND Age IN

(

SELECT TOP 7 Age

FROM Persons

ORDER BY Sallary DESC

)

AND Height IN

(

SELECT TOP 5 height

FROM HeightToWeigth

ORDER BY Weight DESC

);

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:

DELETE FirstTable FROM FirstTable a INNER JOIN SecondTable b ON b.Bid = a.Bid;

DELETE FROM ThirdTable;

DELETE FROM FourthTable WHERE col1=4;

DELETE FROM SecondTable;

DELETE FROM FifthTable WHERE col2=4;

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:

SELECT *

FROM FirstTable

ORDER BY 1;

SELECT *

FROM SecondTable

ORDER BY 1,

SELECT *

FROM ThirdTable

ORDER BY 3;

SELECT *

FROM FourthTable

ORDER BY col1;

SELECT *

FROM FifthTable

ORDER BY col1,

col2 DESC;

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:

SELECT *

FROM FirstTable

WHERE value NOT IN(1, 2, 3);

SELECT *

FROM SecondTable

WHERE value2 NOT IN("a", "b");

SELECT *

FROM ThirdTable

WHERE value NOT IN(1);

SELECT *

FROM FirstTable

WHERE value IN(1, 2, 3);

SELECT *

FROM FirstTable

WHERE value2 IN("a", "b");

SELECT *

FROM FourthTable

INNER JOIN FifthTable ON id1 = id2;

SELECT *

FROM FirstTable a

INNER JOIN FirstTable1 b ON aid = bid;

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:

SELECT *

INTO FirstFailedTable

FROM FirstGoodTable;

SELECT *

INTO SecondFailedTable

FROM SecondGoodTable

WHERE col2 IS NULL;

SELECT *

INTO ThirdFailedTable

FROM FourthTable

INNER JOIN FifthTable ON id1 = id2;

SELECT *

FROM FourthTable

INNER JOIN FifthTable ON id1 = id2;

SELECT *

FROM TenthTable a

INNER JOIN leventhTable b ON aid = bid;

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:

SELECT *

FROM FirstTable

WHERE ISNUMERIC(Col1) <> 1;

SELECT *

FROM SecondTable

WHERE ISNUMERIC(Col1) = 1;

SELECT *

FROM ThirdTable

WHERE ISNUMERIC(Col1) = 1

AND ISNUMERIC(Col2) = 1;

SELECT *

FROM SecondTable

WHERE Col1 LIKE '%[^0-9]%';

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:

SELECT *

FROM FirstTable

WHERE CHARINDEX('test', col1) > 0;

SELECT *

FROM SecondTable

WHERE CHARINDEX('test', col1) = 0;

SELECT *

FROM SecondTable

WHERE CHARINDEX('test', col1) = 0

OR CHARINDEX('test2', col2) = 0;

SELECT *

FROM FourthTable

WHERE Col1 LIKE '%[^0-9]%';

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:

SELECT Col1,

Col2,

(

SELECT MAX(Price)

FROM PriceTable AS b

WHERE a.ID = b.GoodID

) AS MaxPrice

FROM Goods AS a;

SELECT Name

FROM Persons

WHERE Town =

(

SELECT Town

FROM Towns

WHERE [Index] = 55555

);

SELECT Name

FROM Persons

WHERE Town IN

(

SELECT TOP 5 Town

FROM Towns

WHERE [Region] = N'Boston'

);

SELECT Name

FROM Persons

WHERE Town IN

(

SELECT TOP 5 Town

FROM Towns

WHERE [Region] = N'Boston'

)

AND Age IN

(

SELECT TOP 7 Age

FROM Persons

ORDER BY Sallary DESC

);

SELECT Name

FROM Persons

WHERE Town IN

(

SELECT TOP 5 Town

FROM Towns

WHERE [Region] = N'Boston'

)

AND Age IN

(

SELECT TOP 7 Age

FROM Persons

ORDER BY Sallary DESC

)

AND Height IN

(

SELECT TOP 5 height

FROM HeightToWeigth

ORDER BY Weight DESC

);

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:

SELECT *

FROM FirstTable

WHERE col1 = 2

OR col2 > 0;

SELECT *

FROM SecondTable

WHERE col1 = 'abc'

OR col1 = 'def';

SELECT *

FROM SecondTable

WHERE Col1 = 'abc';

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:

INSERT INTO FirstTable(col1, col2) VALUES (1, 2);

INSERT INTO SecondTable(col3, col4) VALUES (5, 7);

INSERT INTO ThirdTable EXECUTE insertProc1;

INSERT INTO FourthTable EXECUTE insertProc2;

INSERT INTO FifthTable EXECUTE ('select * from SixthTable');

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:

CREATE PROCEDURE proc_ROWCOUNTFirstFailedEx

BEGIN

SET ROWCOUNT 5;

DELETE FROM FirstTable;

END;

CREATE PROCEDURE proc_ROWCOUNTSecondFailedEx

BEGIN

SET ROWCOUNT 10;

DELETE FROM SecondTable;

END;

CREATE PROCEDURE proc_ROWCOUNTThirdFailedEx

BEGIN

SET ROWCOUNT 20;

DELETE FROM ThirdTable;

END;

CREATE PROCEDURE proc_ROWCOUNTFirstGoodEx

BEGIN

DELETE FROM FourthTable;

END;

CREATE PROCEDURE proc_ROWCOUNTSecondGoodEx

BEGIN

DELETE FROM FifthTable;

END;

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:

MERGE INTO FirstTable AS t1

USING

(

SELECT *

FROM SecondTable AS t2

)

ON t1.valOld = t2.valNew

WHEN MATCHED

THEN UPDATE SET

Val2 = t2.NewVal2

WHEN NOT MATCHED BY TARGET

THEN

INSERT(Val1,

Val2)

VALUES

(Val1,

Val2

);

MERGE INTO ThirdTable AS t3

USING

(

SELECT *

FROM FourthTable AS t4

)

ON t3.valOld = t4.valNew

WHEN MATCHED

THEN UPDATE SET

Val2 = t4.NewVal2

WHEN NOT MATCHED BY TARGET

THEN

INSERT(Val1,

Val2)

VALUES

(Val1,

Val2

);

MERGE INTO FifthTable AS t5

USING

(

SELECT *

FROM SixthTable AS t6

)

ON t5.valOld = t6.valNew

WHEN MATCHED

THEN UPDATE SET

Val2 = t6.NewVal2

WHEN NOT MATCHED BY TARGET

THEN

INSERT(Val1,

Val2)

VALUES

(Val1,

Val2

);

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:

CREATE PROCEDURE proc_NoTryCatchFirstFailedEx

BEGIN

SELECT *

INTO FirstTable

FROM SecondTable;

END;

CREATE PROCEDURE proc_NoTryCatchSecondFailedEx

BEGIN

DELETE FROM ThirdTable;

END;

CREATE PROCEDURE proc_NoTryCatchThirdFailedEx

BEGIN

UPDATE FourthTable

SET

Col1 = 12;

END;

CREATE PROCEDURE proc_NoTryCatcThirdFailedEx

BEGIN

INSERT INTO FifthTable

VALUES

(12,

21,

);

END;

CREATE PROCEDURE proc_NoTryCatchFirstGoodEx

BEGIN

BEGIN TRY

INSERT INTO SixthTable

VALUES

(12,

21,

);

END TRY

BEGIN CATCH

PRINT 'Error';

END CATCH;

END;

CREATE PROCEDURE proc_NoTryCatchSecondGoodEx

BEGIN

BEGIN TRY

UPDATE SeventhTable

SET

Col1 = 12;

END TRY

BEGIN CATCH

PRINT 'Error';

END CATCH;

END;

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:

CREATE PROCEDURE proc_DBContextFirstFailedEx

BEGIN

DECLARE MyFailedCursor CURSOR

FOR SELECT *

FROM FirstTable;

OPEN MyFailedCursor;

FETCH NEXT FROM MyFailedCursor;

END;

USE SecondDemoDB;

CREATE PROCEDURE proc_DBContextSecondFailedEx

BEGIN

DECLARE MySecondFailedCursor CURSOR

FOR SELECT *

FROM FirstTable;

OPEN MySecondFailedCursor;

FETCH NEXT FROM MySecondFailedCursor;

END;

CREATE PROCEDURE proc_DBContextThirdFailedEx

BEGIN

DECLARE MyThirdFailedCursor CURSOR

FOR SELECT *

FROM FirstTable;

OPEN MyThirdFailedCursor;

FETCH NEXT FROM MyThirdFailedCursor;

CLOSE MyThirdFailedCursor;

END;

USE ThirdDemoDB;

CREATE PROCEDURE proc_DBContextFirstGoodEx

BEGIN

DECLARE MyFirstGoodCursor CURSOR LOCAL

FOR SELECT *

FROM FirstTable;

OPEN MyFirstGoodCursor;

FETCH NEXT FROM MyFirstGoodCursor;

CLOSE MyFirstGoodCursor;

DEALLOCATE MyFirstGoodCursor;

END;

USE FourthDemoDB;

CREATE PROCEDURE proc_DBContextSecondGoodEx

BEGIN

DECLARE MySecondGoodCursor CURSOR GLOBAL READ_ONLY

FOR SELECT *

FROM SecondTable;

OPEN MySecondGoodCursor;

FETCH NEXT FROM MySecondGoodCursor;

CLOSE MySecondGoodCursor;

DEALLOCATE MySecondGoodCursor;

END;

Message:

Batch contains USE statement

Author
Recent Posts

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