Design Rules

Introduction

This analysis group contains design related analysis rules.

Design Rules

Related Topics

SA0001 : Equality and inequality comparisons involving a NULL constant found. Use IS NULL or IS NOT NULL

SA0002 : Variable declared but never referenced or assigned

SA0003 : Variable used but not previously assigned

SA0005 : Non-ANSI outer join syntax

SA0006 : Non-ANSI inner join syntax

SA0008 : Deprecated syntax string_alias = expression

SA0010 : Use TRY..CATCH or check the @@ERROR variable after executing data manipulation statement

SA0011 : SELECT * in stored procedures, views and table-valued functions

SA0012 : Use SCOPE_IDENTITY() instead @@IDENTITY

SA0018 : Support for constants in ORDER BY clause have been deprecated

SA0019 : TOP clause used in a query without an ORDER BY clause

SA0020 : Always use a column list in INSERT statements

SA0021 : Deprecated usage of table hints without WITH keyword

SA0022 : Index type (CLUSTERED or NONCLUSTERED) not specified

SA0029 : Input parameter never used

SA0031 : Avoid GOTO statement to improve readability

SA0034 : Use parentheses to improve readability and avoid mistakes because of logical operator precedence

SA0035 : TODO,HACK or UNDONE phrase found in a comment

SA0036 : DELETE statement without row limiting conditions

SA0037 : UPDATE statement without row limiting conditions

SA0038 : The comparison expression evaluates to TRUE

SA0039 : The comparison expression evaluates to FALSE

SA0041 : Avoid joining with views

SA0050 : Do not create clustered index on UNIQUEIDENTIFIER columns

SA0050B : Do not create clustered index on UNIQUEIDENTIFIER columns

SA0051 : The query is missing a join predicate. This may affect or result more than expected rows

SA0052 : Avoid using undocumented and deprecated stored procedures

SA0053A : Don’t use deprecated TEXT,NTEXT and IMAGE data types

SA0053B : Don’t use deprecated TEXT,NTEXT and IMAGE data types

SA0056 : Index has exact duplicate or overlapping index

SA0057 : Consider using EXISTS predicate instead of IN predicate

SA0058 : Avoid converting dates to string during date comparison

SA0059A : Check database for objects created with different than default or specified collation

SA0059B : Check for usage of collation different than the database default or the specified collation

SA0060 : The sp_xml_preparedocument procedure call is not paired with a following sp_xml_removedocument call

SA0076 : Check UPDATE and DELETE statements for not filtering using all columns of the table’s PRIMARY KEY or UNIQE KEY

SA0077 : Avoid executing dynamic code using EXECUTE statement

SA0078 : Statement is not terminated with semicolon

SA0079 : Avoid using column numbers in ORDER BY clause

SA0080 : Do not use VARCHAR or NVARCHAR data types without specifying length

SA0081 : Do not use DECIMAL or NUMERIC data types without specifying precision and scale

SA0082 : Consider prefixing column names with table name or table alias

SA0085 : Check database objects for missing specific extended properties

EX0006 : Identify possible missing Foreign Keys

EX0015 : Find best clustered index

SA0091 : Setting the QUOTED_IDENTIFIERS or ANSI_NULLS options inside stored procedure, trigger or function will have no effect

SA0092 : The SQL module was created with ANSI_NULLS and/or QUOTED_IDENTIFIER options set to OFF

SA0092B : The SQL module was created with ANSI_NULLS and/or QUOTED_IDENTIFIER options set to OFF

SA0095 : The updated column is a primary key column

SA0096 : The collation of the current database does not match that of the model database

SA0097 : The procedure/function/trigger has cyclomatic complexity above the threshold value

SA0098 : The results from triggers are currently allowed. Consider disabling results from triggers

SA0101 : Avoid using hints to force a particular behavior

SA0102 : Do not use DISTINCT keyword in aggregate functions

SA0103 : Avoid using ISNUMERIC function as it accepts floating point and monetary number

SA0104 : Use CASE statements in conjunction with aggregation to write more robust and better performing queries

SA0105 : Avoid using CHARINDEX function

SA0106 : Avoid OR operator in queries

SA0107 : Avoid using procedural logic with a cursor

SA0108 : Avoid using NOLOCK hint, use isolation levels instead

SA0109 : Avoid joining with subquery which has a TOP clause

SA0110 : Avoid have stored procedure that contains IF statements

SA0111 : Do not use WAITFOR DELAY/TIME statement in stored procedures, functions, and triggers

SA0112A : Avoid IDENTITY columns unless you are aware of their limitations

SA0112B : Avoid IDENTITY columns unless you are aware of their limitations

SA0113 : Do not use SET ROWCOUNT to restrict the number of rows

SA0114 : Duplicate names of objects found

SA0114B : Object with the same name but different type already exists

SA0115 : Ensure variable assignment from SELECT with no rows

SA0116 : Consider using EXISTS,IN or JOIN when usage of = (SELECT * FROM ) and the subquery returns more than column

SA0117 : Use OUTPUT instead of SCOPE_IDENTITY() or @@IDENTITY

SA0118 : Use MERGE instead of INSERT…UPDATE or UPDATE…INSERT statements

SA0119 : Consider aliasing all table sources in the query

SA0120 : Consider using NOT EXISTS,EXCEPT or LEFT JOIN instead of the NOT IN predicate with a subquery

SA0121 : Output parameter is not populated in all code paths

SA0122 : Use ISNULL(Column,Default value) on nullable columns in expressions

SA0124 : The arguments for the COALESCE function must be of the same data type

SA0125 : Avoid use of the SELECT INTO syntax

SA0126 : Operator combines two different types will cause implicit conversion

SA0130 : Explicit error handling for statements between BEGIN TRAN and COMMIT/ROLLBACK TRAN is required

SA0131 : High number of estimated rows found in execution plan

SA0132 : The arguments of the ISNULL function are not of the same data type

SA0133 : Consider storing the result of the Date-Time function which get current time in a variable at the beginning of the statement and use these variable later

SA0134 : Do not interleave DML with DDL statements. Group DDL statements at the beginning of procedures followed by DML statements

SA0136 : Use fully qualified object names in SELECT, UPDATE, DELETE, MERGE and EXECUTE statements

SA0144 : The code following the RETURN or the RAISERROR statements will never be executed

SA0145 : The EOL marker sequence is not the expected {CR}{LF}

SA0146 : The RAISERROR statement with severity above 18 and requires WITH LOG clause

SA0147 : The Cognitive Complexity of the statement should not be too high

SA0148 : Consider using a temporary table instead of a table variable

SA0150 : The procedure grants permissions at the end of its body. Possible missing GO batch separator command

SA0151 : Statements appear after procedures main BEGIN/END block. Possible missing GO command

SA0152 : THROW statement appears as a transaction name in ROLLBACK TRANSACTION

SA0153 : Always specify parameter names when calling stored procedures

SA0155 : Deprecated setting of database option CONCAT_NULL_YIELDS_NULL to OFF

SA0155B : Setting CONCAT_NULL_YIELDS_NULL to OFF is deprecated

SA0156 : Statements CREATE/DROP DEFAULT are deprecated. Use DEFAULT keyword in CREATE/ALTER TABLE

SA0157 : Usage of three and four part column names is deprecated. Two-part names is the standard-compliant behavior

SA0158 : Deprecated usage of space as separator for table hints. Use a comma instead of space

SA0159 : Deprecated use of object name containing only # characters

SA0160 : Deprecated use of @, @@, or names that begin with @@ as Transact-SQL identifiers

SA0161 : Current database uses old SQL Server collation. To take full advantage of SQL Server features, for new development change the default installation settings to use Windows collations

SA0162 : Column created with option ANSI_PADDING set to OFF

SA0163 : Deprecated setting of database options ANSI_PADDING to OFF

SA0163B : Setting ANSI_PADDING to OFF is deprecated

SA0165 : TOP (100) PERCENT found

SA0166 : Avoid altering security within stored procedures

SA0167 : Non-ISO standard comparison operator found

SA0168 : Possible division by zero not handled according the practice

SA0169 : Use @@ROWCOUNT only after SELECT, INSERT, UPDATE, DELETE or MERGE statements

SA0170 : It is recommend to not use CTE unless it is need for hierarchical data

SA0171 : The ROW_NUMBER paging pattern can be replaced with OFFSET FETCH clause

SA0172 : The dynamic SQL is constructed using external parameters, which is not ensured to be safe

SA0173 : COALESCE, IIF, and CASE input expressions containing sub-queries will be evaluated multiple times

SA0174 : The CASE expressions should not rely on short-circuit behavior with aggregate functions or full text search predicates

SA0175 : Extract input expression as a variable in order to ensure it is invariant and avoid unexpected results

SA0176 : Consider merging nested IF statements to improve readability

SA0177 : To improve code readability, put only one statement per line

SA0178 : LIKE operator is used without wildcards

SA0179 : Do not create function and procedures with too many parameters

SA0180 : CASE expression has too many WHEN clauses

SA0181 : The query joins too many table sources

SA0182 : The CASE expressions is missing ELSE clause

SA0183 : The commented out code reduces readability and should be deleted

SA0184 : Redundant pairs of parentheses can be removed

SA0185 : Review the call for unintentionally passing the same value more than once as an argument

SA0186 : Possible missing BEGIN..END block

SA0187 : Duplicated string literals complicate the refactoring

SA0188 : The NULL or NOT NULL constraint not explicitly specified in the table column definition

SA0189 : Store procedure executed without getting a result

SA0190 : Numbered stored procedures are deprecated

SA0191 : Procedure body is not enclosed in BEGIN…END block

SA0192 : Procedure returns more than one result set

SA0193 : Avoid unused labels to improve readability

SA0194 : The ELSE clause is not needed.If it is omitted the CASE expression will still return NULL as default value

SA0196 : Deprecated use of DROP INDEX with two-part index name syntax

SA0197 : The deprecated FASTFIRSTROW hint was encountered

SA0198 : Usage of deprecated GROUP BY ALL syntax encountered

SA0199 : Usage of deprecated COMPUTE clause encountered

SA0232 : The GO batch terminator command found inside comment

SA0233 : Temporary table created but not dropped

SA0234 : It is recommended to use the new TOP(expression) clause syntax

SA0235 : Consider using the AS keyword to specify a column alias instead of the column_alias = expression syntax

SA0236 : The xp_cmdshell system stored procedure used

SA0240 : The stored procedure does not return result code

SA0243 : Avoid INSERT-EXECUTE in stored procedures

SA0244 : Database object created,altered or dropped without specifiying schema name

SA0246 : Stored procedure executed with incorrect arguments

SA0247A : Don’t use FLOAT, REAL, MONEY, SMALLMONEY or SQL_VARIANT data types

SA0247B : Don’t use FLOAT, REAL, MONEY, SMALLMONEY or SQL_VARIANT data types

SA0248 : Stored procedure called with mixing both unnamed and named arguments style

SA0249 : Specify default value for columns added with NOT NULL constraint

SA0250 : Consider calling procedures with named arguments

SA0251 : Subquery used in expression not ensured to return a single value

SA0252 : The referenced object (table, view, procedure or function) is in another database

SA0253 : The current database is hardcoded in object reference

SA0254 : Invalid operation due to cursor closed or not declared

SA0255 : Consider using extended cursor declaration syntax instead of the ISO syntax

SA0256 : A cursor with the same name is declared earlier. Avoid reusing cursor names

SA0257 : The cursor declaration does not fit the performed cursor operations

SA0258 : The number of FETCH statement variables does not match the number of columns in the cursor definition

SA0137 : BEGIN TRANSACTION statement is missing a following COMMIT statement

SA0138 : BEGIN TRANSACTION statement without ROLLBACK statement

SA0139 : The procedure argument type is not compatible with the procedure parameter type

SA0259 : The created object already exists

SA0260 : Parameter defined as nullable, but no default value provided

SA0261 : The number of characters per line should not exceed the configured value

SA0262 : Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

SA0263 : Temporary table is used before it has any data inserted

SA0264 : Temporary table created but not used as table source

SA0265 : COMMIT statement without corresponding BEGIN TRANSACTION statement

SA0266 : ROLLBACK statement without corresponding BEGIN TRANSACTION statement

SA0267 : Table variable is used before it has any data inserted

SA0268 : Table variable is not used as table source

SA0271 : The column alias syntax is not recommended

SA0272 : SELECT statement without row limiting conditions

SA0273 : The USE statement must be the first statement in the script

SA0274 : The first statement in the script, must be USE statement

SA0276 : The type of the assigned value is not compatible with the variable’s declared type

SA0277 : The inserted or updated value is incompatible with the target column type, potentially causing implicit conversion or data truncation

SA0278 : The input expression and when expressions of the simple CASE expression are not of the same data type

SA0279 : The result expressions of the CASE expression are not of the same data type