SQL code smells rules

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:

SELECT DataVal,

MAX(price)

FROM FirstFailedTable

GROUP BY DataVal

HAVING DataVal LIKE '%abc%';

SELECT DataVal,

MAX(price)

FROM SecondFailedTable

GROUP BY DataVal

HAVING DataVal LIKE '%def%';

SELECT DataVal,

MAX(price)

FROM ThirdFailedTable

GROUP BY DataVal

HAVING DataVal LIKE '%111%';

SELECT DataVal,

MAX(price)

FROM FirstGoodTable

GROUP BY DataVal

HAVING SUM(ID) < 20;

SELECT DataVal,

MAX(price)

FROM SecondGoodTable

GROUP BY DataVal

HAVING SUM(ID) < 200;

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:

SELECT *

FROM FirstTable

WHERE value = NULL;

SELECT *

FROM SecondTable

WHERE value = NULL;

SELECT *

FROM ThirdTable

WHERE value != NULL;

SELECT *

FROM FourthTable

WHERE value1 = NULL

OR value2 = NULL

OR value3 = NULL;

SELECT *

FROM FifthTable

WHERE(value1 IS NULL)

OR (value2 IS NOT NULL);

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:

CREATE TABLE #FirstTable

(ID INT,

Name VARCHAR(10)

);

CREATE TABLE #SecondTable

(ID INT,

Name VARCHAR(10)

);

CREATE TABLE ThirdTable

(ID INT,

Name VARCHAR(10)

);

CREATE TABLE #FourthTable

(ID INT,

Name VARCHAR(10)

);

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:

SELECT *

FROM FirstTable

WHERE col1 <> 10;

SELECT *

FROM SecondTable

WHERE col1 != 10;

SELECT *

FROM ThirdTable

WHERE col1 <> 10

AND col2 != 20;

SELECT *

FROM FourthTable

WHERE col1 = 10;

SELECT *

FROM FifthTable

WHERE col2 IS NULL;

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:

CREATE TABLE FirstTable

(ID INT,

col1 VARCHAR(10),

col2 VARCHAR(10)

);

CREATE TABLE SecondTable

(ID INT,

col1 VARCHAR(10),

col2 VARCHAR(10)

);

SELECT *

FROM FirstTable

WHERE col1 <> 10;

SELECT *

FROM SecondTable

WHERE col1 != 10;

CREATE TABLE ThirdTable

(ID INT,

col1 VARCHAR(10),

col2 VARCHAR(10)

);

CREATE TABLE FourthTable

(ID INT,

col1 VARCHAR(10),

col2 VARCHAR(10)

);

SELECT *

FROM ThirdTable

WHERE col1 != 10;

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:

SELECT *

FROM FirstTable

WHERE col1 LIKE '%test%';

SELECT *

FROM SecondTable

WHERE col2 LIKE '%test%';

SELECT *

FROM FifthTable

WHERE col2 LIKE '%test%'

AND col7 LIKE '%abc%';

SELECT *

FROM SixthTable

WHERE col6 LIKE '%test';

SELECT *

FROM ThirdTable

WHERE col2 LIKE 'test%';

SELECT *

FROM FourthTable

WHERE col2 LIKE 'test';

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:

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 FourthTable

WHERE value IN(1, 2, 3);

SELECT *

FROM FifthTable

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

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:

CREATE PROCEDURE proc_CursorFirstFailedEx

BEGIN

DECLARE MyFirstFailedCursor CURSOR

FOR SELECT *

FROM FirstTableFirstTable;

OPEN MyFirstFailedCursor;

FETCH NEXT FROM MyFirstFailedCursor;

END;

CREATE PROCEDURE proc_CursorSecondFailedEx

BEGIN

DECLARE MySecondFailedCursor CURSOR

FOR SELECT *

FROM FirstTableFirstTable;

OPEN MySecondFailedCursor;

FETCH NEXT FROM MySecondFailedCursor;

END;

CREATE PROCEDURE proc_CursoThirdrFailedEx

BEGIN

DECLARE MyThirdFailedCursor CURSOR

FOR SELECT *

FROM FirstTableFirstTable;

OPEN MyThirdFailedCursor;

FETCH NEXT FROM MyThirdFailedCursor;

CLOSE MyThirdFailedCursor;

END;

CREATE PROCEDURE proc_CursorFirstGoodExAS

BEGIN

SELECT *

FROM FirstTable;

END;

CREATE PROCEDURE proc_CursorSecondGoodEx

BEGIN

SELECT *

FROM SecondTable;

END;

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:

CREATE TABLE FirstTable

(ID INT,

Name VARCHAR(10)

);

CREATE TABLE SecondTable

(ID INT,

Name VARCHAR(10)

);

CREATE TABLE ThirdTable

(ID INT,

Name VARCHAR(10)

);

CREATE TABLE FourthTable

(ID INT,

Name VARCHAR(10),

CONSTRAINT pk1 PRIMARY KEY CLUSTERED(ID)

);

CREATE TABLE FifthTable

(ID INT,

Name VARCHAR(10),

CONSTRAINT pk2 PRIMARY KEY CLUSTERED(ID)

);

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:

CREATE PROCEDURE proc_RECOMPILEFirstFailedEx

WITH RECOMPILE

BEGIN

EXEC sp_blockcnt;

END;

CREATE PROCEDURE proc_RECOMPILESecondFailedEx

WITH RECOMPILE

BEGIN

EXEC sp_blockcnt;

END;

CREATE PROCEDURE proc_RECOMPILEThirdFailedEx

WITH RECOMPILE

BEGIN

EXEC sp_blockcnt;

END;

CREATE PROCEDURE proc_RECOMPILEFirstGoodEx

BEGIN

DECLARE @handle INT;

EXEC sp_xml_preparedocument

@handle OUTPUT;

END;

CREATE PROCEDURE proc_RECOMPILESecondGoodEx

BEGIN

DECLARE @handle INT;

EXEC sp_xml_preparedocument

@handle OUTPUT;

END;

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:

CREATE TABLE Logins

(LoginName NVARCHAR(100) NOT NULL,

Pass NVARCHAR(50) NOT NULL

);

CREATE TRIGGER triggerSPCallFirstFailedEx ON Logins

FOR INSERT

SELECT *

FROM Logins;

EXEC PassLog;

CREATE TRIGGER triggerSPCallSecondFailedEx ON Logins

FOR UPDATE

SELECT *

FROM Logins;

EXEC PassLog;

CREATE TRIGGER triggerSPCallThirdFailedEx ON Logins

FOR DELETE

SELECT *

FROM Logins;

EXEC PassLog;

CREATE TRIGGER triggerSPCallFirstGoodEx ON Logins

FOR INSERT, UPDATE, DELETE

PRINT 'test2';

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:

CREATE PROCEDURE proc_TransSPFirstFailedEx

BEGIN

BEGIN TRANSACTION;

BEGIN TRY

INSERT INTO testUniq(ID)

VALUES(5);

INSERT INTO testUniq(ID)

VALUES(5);

END TRY

BEGIN CATCH

ROLLBACK;

THROW;

END CATCH;

COMMIT;

END;

CREATE PROCEDURE proc_TransSPSecondFailedEx

BEGIN

BEGIN TRANSACTION;

BEGIN TRY

INSERT INTO testUniq(ID)

VALUES(5);

INSERT INTO testUniq(ID)

VALUES(5);

END TRY

BEGIN CATCH

ROLLBACK;

THROW;

END CATCH;

COMMIT;

END;

CREATE PROCEDURE proc_TransSPThirdFailedEx

BEGIN

BEGIN TRANSACTION;

BEGIN TRY

INSERT INTO testUniq(ID)

VALUES(5);

INSERT INTO testUniq(ID)

VALUES(5);

END TRY

BEGIN CATCH

ROLLBACK;

THROW;

END CATCH;

COMMIT;

END;

CREATE PROCEDURE proc_TransSPFirstGoodEx

BEGIN

BEGIN TRANSACTION;

BEGIN TRY

INSERT INTO testUniq(ID)

VALUES(5);

INSERT INTO testUniq(ID)

VALUES(5);

END TRY

BEGIN CATCH

PRINT 'error';

END CATCH;

COMMIT;

END;

CREATE PROCEDURE proc_TransSPSecondGoodEx

BEGIN

BEGIN TRANSACTION;

BEGIN TRY

INSERT INTO testUniq(ID)

VALUES(5);

INSERT INTO testUniq(ID)

VALUES(5);

END TRY

BEGIN CATCH

PRINT 'error';

END CATCH;

COMMIT;

END;

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:

SET ANSI_NULLS OFF;

SET FORCEPLAN OFF

SET CONCAT_NULL_YIELDS_NULL OFF;

SET ANSI_PADDING OFF;

SET FORCEPLAN OFF;

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:

SELECT COUNT(*)

FROM FirstTable

WHERE Col1 > 0;

SELECT COUNT(*)

FROM SecondTable

WHERE Col1 < 0;

SELECT COUNT(*)

FROM ThirdTable

WHERE Col2 IS NULL;

SELECT COUNT(*)

FROM FourthTable;

SELECT id

FROM FifthTable

WHERE Col14 > 0;

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:

CREATE PROCEDURE proc_CursorReadOnlyFirstFailedEx

BEGIN

DECLARE MyFailedCursor CURSOR

FOR SELECT *

FROM FirstTableFirstTable;

OPEN MyFailedCursor;

FETCH NEXT FROM MyFailedCursor;

END;

CREATE PROCEDURE proc_CursorReadOnlyFirstFailedEx

BEGIN

DECLARE MySecondFailedCursor CURSOR

FOR SELECT *

FROM FirstTableFirstTable;

OPEN MySecondFailedCursor;

FETCH NEXT FROM MySecondFailedCursor;

END;

CREATE PROCEDURE proc_CursorReadOnlyThirdFailedEx

BEGIN

DECLARE MyThirdFailedCursor CURSOR

FOR SELECT *

FROM FirstTableFirstTable;

OPEN MyThirdFailedCursor;

FETCH NEXT FROM MyThirdFailedCursor;

CLOSE MyThirdFailedCursor;

END;

CREATE PROCEDURE proc_CursorReadOnlyFirstGoodEx

BEGIN

DECLARE MySecondGoodCursor CURSOR READ_ONLY

FOR SELECT *

FROM SecondTableSecondTable;

OPEN MySecondGoodCursor;

FETCH NEXT FROM MySecondGoodCursor;

CLOSE MySecondGoodCursor;

DEALLOCATE MySecondGoodCursor;

END;

Message:

Non-actionable Cursor is not declared as readonly

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