Best practice rules
Published 16 November 2017
Adhere to good industry practices.
BP001 – Index type is not specified 
The index type (whether it is CLUSTERED or NONCLUSTERED) is not specified explicitly in the CREATE INDEX statement.
You can only have one clustered index on a table, of course, and this choice has a lot of influence on the performance of queries, so you should take care to select wisely. The primary key is often, but not only, the correct choice.
BP002 – ORDER BY clause with constants
Constants are being used in the ORDER BY clause.
The use of constants in the ORDER BY is deprecated for removal in the future. They make ORDER BY statements more difficult to understand.
BP003 – SELECT in trigger
One or more SELECT statements were found in a trigger.
The trigger should never return data to a client. It is possible to place a SELECT statement in a trigger, but it serves no practical, useful purpose, and can have unexpected effects.
BP004 – INSERT without column list
The column list for the insert statement is missing.
The INSERT statement need not have a column list, but omitting it assumes certain columns in a particular order. It likely to cause errors if the table in to which the inserts will be made is changed, particularly with table variables where insertions are not checked. Column lists also make code more intelligible.
BP005 – Asterisk in select list
There is an asterisk instead of a column list in a SELECT statement.
SELECT * FROM in IF EXISTS statements are fine, but SELECT * in other contexts assumes certain columns in a particular order, which may not last. Also, results should always consist of just the columns you need.
BP006 – TOP without ORDER BY
TOP is being used in a SELECT statement without a subsequent ORDER BY clause.
This is legal in SQL but meaningless because asking for the TOP 10 rows implies a certain order, and tables have no implicit logical order.
BP007 – Variable length datatype without explicit length
Variable length datatype is declared without explicit length (VARCHAR, VARBINARY and NVARCHAR).
A variable length datatype that is declared without an explicit length is a shorthand for specifying a length of 1.
BP008 – CAST/CONVERT to var types without length
The length of VARCHAR, VARBINARY and NVARCHAR datatype in a CAST or CONVERT clause wasn’t explicitly specified.
When you convert a datatype to a varchar, you do not have to specify the length. If you don’t do so, SQL Server will use a Varchar length sufficient to hold the string. It is better to specify the length because SQL Server has no idea what length you may subsequently need.
BP009 – Avoid var types of length 1 or 2
You have used a Variable length datatype (NVARCHAR , VARCHAR or BINARY) of length 1 or 2.
Very small CHAR and BINARY values are more economically stored than their VAR equivalents. Specify the datatype of the fixed-length version.
BP010 – Usage of @@IDENTITY
@@IDENTITY is being used to get the value of the last identity insertion.
If you have a trigger on the table, the value can sometimes be wrong. The @@IDENTITY function returns the last identity created in the same session whereas SCOPE_IDENTITY() function returns the last identity created in the same scope as well. Using SCOPE_IDENTITY() is safer.
BP011 – NULL comparison or addition/substring
A comparison or expression is using NULL without explicit provision for a NULL value.
Any expression or comparison that involves a NULL value will produce a NULL result. Expressions need to use IS [NOT] NULL and the ISNULL/COALESCE function to handle NULL values appropriately.
BP012 – CASE without ELSE
The CASE statement doesn't specify what happens when all WHEN expressions evaluate to FALSE.
The ELSE clause can be omitted but unless you use the ELSE clause of the CASE statement, you will find that a NULL is returned if all WHEN expressions return FALSE. If you really want to do this, make it explicit so the code is easier to understand.
BP013 – EXECUTE(string) is used
EXECUTE (string) is being used to execute a SQL batch in a string.
sp_ executesql supports parameter substitution, and generates execution plans that are more likely to be reused by SQL Server.
BP014 – [NOT] NULL option is not specified in CREATE/DECLARE TABLE statement
A column definition has not specified that a column is NULL or NOT NULL.
Because the default of allowing NULLs can be changed with the database setting ‘ANSI_NULL_DFLT_ON’, you should explicitly define a column as NULL or NOT NULL for noncomputed columns or, if you use a user-defined data type, that you allow the column to use the default nullability of the data type. Sparse columns must always allow NULL.
NOTE: This issue is only shown once for each table definition.
BP015 – Scope of cursor (LOCAL/GLOBAL) is not specified
You have not explicitly defined the scope of a cursor.
When you define a cursor with the DECLARE CURSOR statement you can, and should, define the scope of the cursor name. GLOBAL means that the cursor name should be global to the connection. LOCAL specifies that the cursor name is LOCAL to the stored procedure, trigger, or batch containing the DECLARE CURSOR statement.
BP016 – Return without result code
You have a stored procedure that does not return a result code.
When you use the EXECUTE command to execute a stored procedure, or call the stored procedure from an application, an integer is returned that can be assigned to a variable. It is generally used to communicate the success of the operation.
BP017 – DELETE statement without WHERE clause
A DELETE statement has omitted that WHERE clause, which would delete the whole table.
It is very easy to delete an entire table when you mean to delete just one or more rows.
BP018 – UPDATE statement without WHERE clause
The UPDATE statement has omitted the WHERE clause, which would update every row in the table.
It is very easy to delete an entire table when you mean to delete just one or more rows. Delete statements should also be in a transaction so you can check the result before committing.
BP020 – Column created with option ANSI_PADDING set to OFF
Column created with option ANSI_PADDING set to OFF.
BP021 – Table does not have clustered index
Most tables should have a clustered index
BP022 – Money/SmallMoney datatype is used
The MONEY data type confuses the storage of data values with their display, though it clearly suggests, by its name, the sort of data held. Using the DECIMAL data type is almost always better.
BP023 – Float/real datatype is used
The FLOAT (8 byte) and REAL (4 byte) data types are suitable only for specialist scientific use since they are approximate types with an enormous range (-1.79E+308 to -2.23E-308, and 2.23E-308 to 1.79E+308, in the case of FLOAT).
Any other use needs to be regarded as suspect, and a FLOAT or REAL used as a key or found in an index needs to be investigated.
The DECIMAL type is an exact data type and has an impressive range from -10^38+1 through 10^38-1. Although it requires more storage than the FLOAT or REAL types, it is generally a better choice.
BP024 – sql_variant datatype is used
The sql_variant type is not your typical data type. It stores values from a number of different data types and is used internally by SQL Server. It is hard to imagine a valid use in a relational database. It cannot be returned to an application via ODBC except as binary data, and it isn’t supported in Microsoft Azure SQL Database.
