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

Artikel
02/04/2013

RuleId	SR0014
Category	Microsoft.Design
Breaking Change	Non-breaking

Cause

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

Rule Description

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.

Note

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.` Note 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`

How to Fix Violations

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).

When to Suppress Warnings

You should not suppress this kind of warning.

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

Dela via