SR0014: Data loss might occur when casting from {Type1} to {Type2}
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 (T-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.
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