T-SQL design issues

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

When you analyze the T-SQL code in your database project, one or more warnings might be categorized as design issues. You should address design issues to avoid the following situations:

• Subsequent changes to your database might break applications that depend on it.
• The code might not produce the expected result.
• The code might break if you run it with future releases of SQL Server.

In general, you shouldn't suppress a design issue because it might break your application, either now or in the future.

The provided rules identify the following design issues:

• SR0001: Avoid SELECT * in stored procedures, views, and table-valued functions
• SR0008: Consider using SCOPE_IDENTITY instead of @@IDENTITY
• SR0009: Avoid using types of variable length that are size 1 or 2
• SR0010: Avoid using deprecated syntax when you join tables or views
• SR0013: Output parameter (parameter) is not populated in all code paths
• SR0014: Data loss might occur when casting from {Type1} to {Type2}

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

If you use a wildcard character in a stored procedure, view, or table-valued function to select all columns in a table or view, the number or shape of returned columns might change if the underlying table or view changes. The shape of a column is a combination of its type and size. This variance could cause problems in applications that consume the stored procedure, view, or table-valued function because those consumers will expect a different number of columns.

How to fix violations

You can protect consumers of the stored procedure, view, or table-valued function from schema changes by replacing the wildcard character with a fully qualified list of column names.

Example

The following example first defines a table that is named [Table2] and then defines two stored procedures. The first procedure contains a SELECT *, which violates rule SR0001. The second procedure avoids SELECT * and explicitly lists the columns in the SELECT statement.

CREATE TABLE [dbo].[Table2]
(
[ID] INT NOT NULL IDENTITY(0, 1),
[c1] INT NOT NULL,
[Comment] NVARCHAR (50)
)
ON [PRIMARY]

CREATE PROCEDURE [dbo].[procWithWarning]
AS
BEGIN
-- Contains code that breaks rule SR0001
SELECT *
FROM [dbo].[Table2]
END

CREATE PROCEDURE [dbo].[procFixed]
AS
BEGIN
-- Explicitly lists the column names in a SELECT statement
SELECT [dbo].[Table2].[ID], [dbo].[Table2].[c1], [dbo].[Table2].[Comment]
FROM [dbo].[Table2]
END

SR0008: Consider using SCOPE_IDENTITY instead of @@IDENTITY

Because @@IDENTITY is a global identity value, it might have been updated outside the current scope and obtained an unexpected value. Triggers, including nested triggers used by replication, can update @@IDENTITY outside your current scope.

How to fix violations

To resolve this issue you must replace references to @@IDENTITY with SCOPE_IDENTITY, which returns the most recent identity value in the scope of the user statement.

Example

In the first example, @@IDENTITY is used in a stored procedure that inserts data into a table. The table is then published for merge replication, which adds triggers to tables that are published. Therefore, @@IDENTITY can return the value from the insert operation into a replication system table instead of the insert operation into a user table.

The Sales.Customer table has a maximum identity value of 29483. If you insert a row into the table, @@IDENTITY and SCOPE_IDENTITY() return different values. SCOPE_IDENTITY() returns the value from the insert operation into the user table, but @@IDENTITY returns the value from the insert operation into the replication system table.

The second example shows how you can use SCOPE_IDENTITY() to access the inserted identity value and resolve the warning.

CREATE PROCEDURE [dbo].[ProcedureWithWarning]
@param1 INT,
@param2 NCHAR(1),
@Param3 INT OUTPUT
AS
BEGIN
INSERT INTO Sales.Customer ([TerritoryID],[CustomerType]) VALUES (@param1,@param2);

SELECT @Param3 = @@IDENTITY
END

CREATE PROCEDURE [dbo].[ProcedureFixed]
@param1 INT,
@param2 NCHAR(1),
@param3 INT OUTPUT
AS
BEGIN
INSERT INTO Sales.Customer ([TerritoryID],[CustomerType]) VALUES (@param1,@param2);

SELECT @Param3 = SCOPE_IDENTITY()
END

SR0009: Avoid using types of variable length that are size 1 or 2

When you use data types of variable length such as VARCHAR, NVARCHAR, and VARBINARY, you incur an additional storage cost to track the length of the value stored in the data type. In addition, columns of variable length are stored after all columns of fixed length, which can have performance implications. You'll also receive a warning if you declare a type of variable length, such as VARCHAR, but you specify no length. This warning occurs because, if unspecified, the default length is 1.

How to fix violations

If the length of the type will be very small (size 1 or 2) and consistent, declare them as a type of fixed length, such as CHAR, NCHAR, and BINARY.

Example

This example shows definitions for two tables. The first table declares a string of variable length to have length 2. The second table declares a string of fixed length instead, which avoids the warning.

CREATE TABLE [dbo].[TableWithWarning]
(
[ID] INT NOT NULL IDENTITY(0, 1),
[c1] INT NOT NULL PRIMARY KEY,
[c2] INT,
[c3] INT,
[SmallString] VARCHAR(2)
)
ON [PRIMARY]

CREATE TABLE [dbo].[FixedTable]
(
[ID] INT NOT NULL IDENTITY(0, 1),
[c1] INT NOT NULL PRIMARY KEY,
[c2] INT,
[c3] INT,
[SmallString] CHAR(2)
)
ON [PRIMARY]

Data for types of variable length is physically stored after data for types of fixed length. Therefore, you'll cause data movement if you change a column from variable to fixed length in a table that isn't empty.

SR0010: Avoid using deprecated syntax when you join tables or views

Joins that use the deprecated syntax fall into two categories:

• Inner Join: For an inner join, the values in the columns that are being joined are compared by using a comparison operator such as =, <, >=, and so forth. Inner joins return rows only if at least one row from each table matches the join condition.
• Outer Join: Outer joins return all rows from at least one of the tables or views specified in the FROM clause, as long as those rows meet any WHERE or HAVING search condition. If you use = or = to specify an outer join, you're using deprecated syntax.

How to fix violations

To fix a violation in an inner join, use the INNER JOIN syntax.

To fix a violation in an outer join, use the appropriate OUTER JOIN syntax. You have the following options:

• LEFT OUTER JOIN or LEFT JOIN
• RIGHT OUTER JOIN or RIGHT JOIN

Examples of the deprecated syntax and the updated syntax are provided in the following examples. More information on joins can be found at Joins.

Examples

The six examples demonstrate the following options:

1. Example 1 demonstrates the deprecated syntax for an inner join.
2. Example 2 demonstrates how you can update Example 1 to use current syntax.
3. Example 3 demonstrates the deprecated syntax for a left outer join.
4. Example 4 demonstrates how you can update Example 2 to use current syntax.
5. Example 5 demonstrates the deprecated syntax for a right outer join.
6. Example 6 demonstrates how you can update Example 5 to use current syntax.

-- Example 1: Deprecated syntax for an inner join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2, [dbo].[Table1] T1
WHERE [T1].[ID] = [T2].[ID]

-- Example 2: Current syntax for an inner join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] AS T2
INNER JOIN [dbo].[Table1] as T1
ON [T2].[ID] = [T2].[ID]

-- Example 3: Deprecated syntax for a left outer join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2, [dbo].[Table1] T1
WHERE [T1].[ID] *= [T2].[ID]

-- Example 4: Fixed syntax for a left outer join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] AS T2
LEFT OUTER JOIN [dbo].[Table1] as T1
ON [T2].[ID] = [T2].[ID]

-- Example 5: Deprecated syntax for a right outer join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] T2, [dbo].[Table1] T1
WHERE [T1].[ID] =* [T2].[ID]

-- Example 6: Fixed syntax for a right outer join
SELECT [T2].[c3], [T1].[c3]
FROM [dbo].[Table2] AS T2
RIGHT OUTER JOIN [dbo].[Table1] as T1
ON [T2].[ID] = [T2].[ID]

SR0013: Output parameter (parameter) isn't populated in all code paths

This rule identifies code in which the output parameter isn't set to a value in one or more code paths through the stored procedure or function. This rule doesn't identify in which paths the output parameter should be set. If multiple output parameters have this problem, one warning appears for each parameter.

How to fix violations

You can correct this issue in one of two ways. You can fix this issue most easily if you initialize the output parameters to a default value at the start of the procedure body. As an alternative, you can also set the output parameter to a value in the specific code paths in which the parameter isn't set. However, you might overlook an uncommon code path in a complex procedure.

Important

Specifying a value within the procedure declaration, such as CREATE PROC MyProcedure (@param1 INT = 10 OUTPUT) will not resolve the issue. You must assign a value to the output parameter within the procedure body.

Example

The following example shows two simple procedures. The first procedure doesn't set the value of the output parameter, @Sum. The second procedure initializes the @Sum parameter at the start of the procedure, which ensures that the value will be set in all code paths.

CREATE PROCEDURE [dbo].[procedureHasWarning]
(
@Value1 BIGINT,
@Value2 INT,
@Value3 INT,
@Sum INT OUTPUT
)
AS
BEGIN
-- No initialization of the output parameter
--
-- Additional statements here.
--
RETURN 0;
END
--
CREATE PROCEDURE [dbo].[procedureFixed]
(
@Value1 BIGINT,
@Value2 INT,
@Value3 INT,
@Sum INT OUTPUT
)
AS
BEGIN
-- Initialize the out parameter
SET @Sum = 0;
--
-- Additional statements here
--
RETURN 0;
END

SR0014: Data loss might occur when casting from {Type1} to {Type2}

If data types are inconsistently assigned to columns, variables, or parameters, they're implicitly converted when the Transact-SQL code that contains those objects is run. This type of conversion not only reduces performance but also, in some cases, causes subtle loss of data. For example, a table scan might run if every column in a WHERE clause must be converted. Worse, data might be lost if a Unicode string is converted to an ASCII string that uses a different code page.

This rule does NOT:

• Check the type of a computed column because the type isn't known until run-time.
• Analyze anything inside a CASE statement. It also doesn't analyze the return value of a CASE statement.
• Analyze the input parameters or return value of a call to ISNULL

This table summarizes the checks covered by the rule SR0014:

Language construct	What is Checked	Example
Default value of parameters	Parameter data type	CREATE PROCEDURE p1(@p1 INT = 1) AS BEGIN END
CREATE INDEX predicate	Predicate is Boolean	CREATE INDEX index1 ON table1 (column1) WHERE column1 > 10
Arguments of LEFT or RIGHT functions	String argument type and length	SET @v = LEFT('abc', 2)
Arguments of CAST and CONVERT functions	Expression and types are valid	SET @v = CAST('abc' AS CHAR(10))
SET statement	Left side and right side have compatible types	SET @v1 = 'xyz' SELECT @v1 = c1 FROM t1
IF statement predicate	Predicate is Boolean	IF (@v > 10)
WHILE statement predicate	Predicate is Boolean	WHILE (@v > 10)
INSERT statement	Values and columns are correct	INSERT INTO t1(c1, c2) VALUES (99, 'xyz') INSERT INTO t1 SELECT c1 FROM t2.
SELECT WHERE predicate	Predicate is Boolean	SELECT * FROM t1 WHERE c1 > 10
SELECT TOP expression	Expression is an Integer or Float type	SELECT TOP 4 * FROM t1 SELECT TOP 1.5 PERCENT * FROM t1
UPDATE statement	Expression and column have compatible types	UPDATE t1 SET c1 = 100
UPDATE predicate	Predicate is Boolean	UPDATE t1 SET c1 = 100 WHERE c1 > 100
UPDATE TOP expression	Expression is an Integer or Float type	UPDATE TOP 4 table1
DELETE PREDICATE	Predicate is Boolean	DELETE t1 WHERE c1 > 10
DELETE TOP expression	Expression is an Integer or Float type	DELETE TOP 2 FROM t1
DECLARE variable declaration	Initial value and data type are compatible	DECLARE @v INT = 10
EXECUTE statement arguments and return type	Parameters and arguments	CREATE PROCEDURE p1 (@p1 INT) AS GO EXECUTE p1 100 EXECUTE @v1 = p1 100
RETURN statement	RETURN expression has a compatible data type	CREATE FUNCTION f1() RETURNS INT AS BEGIN RETURN 100 END
MERGE statement conditions	Condition is Boolean	MERGE t1 USING t2 ON t1.c1 = t2.c2 WHEN t1.c1 > 10 THEN DELETE

How to fix violations

You can avoid and resolve these issues by assigning data types consistently and by explicitly converting types where they're needed. For more information about how to explicitly convert data types, see this page on the Microsoft Web site: CAST and CONVERT (Transact-SQL).

Example

This example shows two stored procedures that insert data into a table. The first procedure, procWithWarning, will cause an implicit conversion of a data type. The second procedure, procFixed, shows how you can add an explicit conversion to maximize performance and retain all data.

CREATE TABLE [dbo].[Table2]
(
[ID] INT NOT NULL IDENTITY(0, 1),
[c1] INT NOT NULL,
[c2] INT NOT NULL,
[c3] BIGINT NOT NULL,
[Comment] VARCHAR (25)
)
ON [PRIMARY]

CREATE PROCEDURE [dbo].[procWithWarning]
(
@Value1 INT,
@Value2 INT,
@Value3 BIGINT,
@Comment CHAR(30)
)
AS
BEGIN
INSERT INTO [Table2] ([c1], [c2], [c3], Comment)
VALUES (@Value1, @Value2, @Value3, @Comment)

END

CREATE PROCEDURE [dbo].[procFixed]
(
@Value1 INT,
@Value2 INT,
@Value3 BIGINT,
@Comment CHAR(10)
)
AS
BEGIN
INSERT INTO [Table2] ([c1], [c2], [c3], Comment)
VALUES (@Value1, @Value2, @Value3, CAST(@Comment AS VARCHAR(25)))

END

Related content

• SQL code analysis to improve code quality
• T-SQL naming issues
• T-SQL performance issues
• Analyze T-SQL code to find defects