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

RuleId

SR0014

Category

Microsoft.Design

Breaking Change

Non-breaking

The data type for a column, variable, or parameter is being converted implicitly to another data type.

If data types are inconsistently assigned to columns, variables, or parameters, they are 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 is not known until run-time.

  • Analyze anything inside a CASE statement. It also does not analyze the return value of a CASE statement.

  • Analyze the input parameters or return value of a call to ISNULL

SQL CLR Objects

For SQL Server Common Language Run-time (SQL CLR) objects, the following checks are performed:

Object Type

Verifies Type Compatibility

Verifies Potential Data Loss

Columns

Yes

No

Stored Procedure and Function Parameters

No

No

Variables

No

No

XML Types

No

No

When you assign one object to another and both are SQL CLR object types, they must be the same type or a warning will be generated. You can explicitly convert only the following to a SQL CLR object type or a warning appears: binary, varbinary, char, nchar, varchar, or nvarchar.

System Functions

Return type is checked for the following system functions: @@ERROR, @@FETCH_STATUS, @@IDENTITY, @@ROWCOUNT, @@TRANCOUNT, CHECKSUM, CHECKSUM_AGG, COUNT, COUNT_BIG, GROUPING, STDEV, STDEVP, VAR, ARP, RANK, DENSE_RANK, NTILE, ROW_NUMBER, CURSOR_STATUS, SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME, DATEDIFF, DATENAME, DATEPART, DAY, MONTH, YEAR, CURRENT_TIMESTAMP, GETDATE, GETUTCDATE, AVG, SUM, MIN, MAX, DATEADD, SWITCHOFFSET, TODATETIMEOFFSET, and ISNULL.

NoteNote

No check is performed to ensure that the inputs are valid in the function context except for the LEFT, RIGHT, CONVERT, and CAST functions. For example, no warning appears for SUM(datetime2 type) because database code analysis does not understand what type of input is expected by the SUM function. A warning will appear is if there is an issue with the input expression itself, for example if you specified SUM(money + real).

Specific Checks that are Performed

The following table describes specific checks that are performed, with an example for each:

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.
NoteNote
Wildcards are not verified. For example: INSERT INTO t1 SELECT * 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

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

You should not suppress this kind of warning.

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

Community Additions

ADD
Show: