Here is the list of all issues (with short descriptions) that SQL Code Guard can find.
Best practice issues
Index type (CLUSTERED or NONCLUSTERED) is not specified in CREATE INDEX statement.
It is not recommended to use constants in ORDER BY clause.
Trigger should not return data to a client.
It is recommended to specify column list explicitly. Table variables are not checked
It is not recommended to use asterisk in select list.
Usually TOP without ORDER BY is meaningless.
Explicitly specify length.
Explicitly specify the length.
It is not recommended to use @@identity because of side effects. Use SCOPE_IDENTITY instead.
Operations with NULL can produce undesired results. Use IS [NOT] NULL and ISNULL/COALESCE function.
It is recommended to explicitly specify a default action.
It is not recommended to use EXEC to run dynamic SQL. Use sp_executesql instead.
It is recommended to explicitly specify [NOT] NULL option in a table column definition.
It is recommended to specify the cursor scope explicitly.
Table hint without WITH keyword. Use WITH keyword.
WRITETEXT,UPDATETEXT and READTEXT statements are deprecated.
GROUP BY ALL clause is depracated. Use custom case-by-case solution with UNION or derived table.
COMPUTE and COMPUTE BY clauses are deprecated. Use ROLLUP instead
FASTFIRSTROW table hint is deperecated. Use [FAST n] instead.
SETUSER statement is deprecated. Use EXECUTE AS instead.
TAPE as backup device is deprecated.
PASSWORD/MEDIAPASSWORD options in BACKUP/RESTORE statement are deprecated.
DBCC DBREINDEX statement is deprecated. Use ALTER INDEX REBUILD instead.
DBCC CONCURRENCYVIOLATION is deprecated.
DBCC INDEXDEFRAG is deprecated. Use ALTER INDEX DEFRAG instead.
DBCC SHOWCONTIG is deprecated. Use sys.dm_db_index_physical_stats instead.
SET ANSI_NULLS,ANSI_PADDING,CONCAT_NULL_YIELDS_NULL,REMOTE_PROC_TRANSACTIONS,OFFSETS options are deprecated.
SET ROWCOUNT option is deprecated. Use TOP(n) instead.
READONLY and READWRITE options are deprecated. Use READ_ONLY and READ_WRITE options.
TORN_PAGE_DETECTION option is deprecated.
Non-ANSI joins (via *= or =*) are deprecated, use LEFT/RIGHT OUTER join syntax instead.
ALL option in GRANT/DENY/REVOKE statement is deprecated
System table or view is deprecated.
Numbered procedures are deprecated. Do not use numbered procedures
Three-part and four-part column references in SELECT list are deprecated. Two-part names is the standard-compliant behavior.
Variable type is not fully compatible with procedure parameter type.
Literal type is not fully compatible with procedure parameter type.
Subquery may return more than one row.
A named parameter is not found in parameter list of a procedure.
It is not recommended to use the position notation after the named notation.
Parameter is not passed to a procedure and no default is provided.
Procedure parameter is not defined as OUTPUT, but marked as OUTPUT in procedure call statement.
Procedure parameter is defined as OUTPUT, but not marked as OUTPUT in procedure call statement.
Number of passed parameters exceeds the number of procedure parameters.
The cursor definition is not found in the current scope. OPEN statement will cause an error.
The cursor definition is not found in the current scope. FETCH statement will cause an error.
The cursor definition is not found in the current scope. CLOSE statement will cause an error.
The cursor definition is not found in the current scope. DEALLOCATE statement will cause an error.
It is not recommended to use the asterisk in select list when defining a cursor.
Number of variables in FETCH statement differs from the number of columns in cursor definition.
Take notice of using hardcoded references to other databases.
Take notice of using hardcoded names.
It is recommended to specify parameter name while calling a stored procedure.
Take notice of using BEGIN TRANSACTION without ROLLBACK TRANSACTION.
Take notice of using ROLLBACK TRANSACTION without BEGIN TRANSACTION.
It is not possible to close an unopened cursor.
It is not possible to fetch rows from an unopened cursor.
It is recommended to specify a cursor as updatable before performing UPDATE/DELETE operation on the cursor.
It is not recommended to start a user stored procedure name with sp_ as this prefix is used in built in stored procedures.
Adding NOT NULL column without default value to a table with data will fail
Avoid using ISNUMERIC() function because it accepts int, numeric, float and money numbers
Timeout during parsing
Unable to parse SQL code
This issue is not registered for query with single table in FROM clause.
It is recommended to always specify schema name
It is recommended to always specify schema name
It is not recommended to use this method of creating a table. Create the table manually by means of CREATE TABLE statement.
It is not recommended to use index hints
It is not recommended to use join hints
It is not recommended to use table hints
It is not recommended to use query hints
It is not recommended to set NOCOUNT to OFF
It is recommended to set NOCOUNT to ON before any DML statements
It is not recommended to mix DML and DDL statement because recompilation issues may arise
Trigger should not return data back to a client.
Setting this option from within batch leads to batch recompilation.
It is not recommended to use COUNT() in such manner. Use [NOT] EXISTS() instead.
It is not recommended to use SET FORCEPLAN OFF because of possibility of performance degradation.
It is recommended to declare cursor with appropriate scroll options(FORWARD_ONLY,FAST_FORWARD etc).
It is recommended to deallocate a cursor explicitly.
It is not recommended to use the UDF that returns const value in JOIN, WHERE clauses or in select list. It is better to store the UDF value in a variable.
It is recommended to declare a cursor as readonly if no UPDATE or DELETE at the cursor position is planned.
Consider using [NOT] EXISTS instead of [NOT] IN (subquery).
Script should end with GO
Script should end with empty line
There should be no USE statement in batch
Use ANSI-style joins via JOIN
It is recommended to specify alias via AS
It is recommended to enclose a routine body in BEGIN...END block.
Use extended syntax instead
It is recommended to use BEGIN...END as a wrapper for a block of code in IF or ELSE statements.
It is recommended to use new style TOP clause - TOP(n).
More than one cursor has the same name.
Use named parameters when calling procedure (exec dbo.Prcoedure @Parameter1=value,@parameter2=...). Issue registered once per procedure call
Avoid using GOTO to improve readability
Use alias for all table sources
Consider using table variable instead of temporary table
Consider using temporary table instead of table variable
Use ANSI-style NOT_EQUAL operator (<>)