Often, I see database developers relying on implicit conversions more than they should. Implicit conversions can use unexpected problems if not used judiciously.

In this post, I will demonstrate how implicit conversions can cause issues when trying to insert data in a table using the VALUES clause. Assume the following sample object into which we want to insert the data.

USE tempdb;
GO
--Demo objects
BEGIN
    IF OBJECT_ID('dbo.BewareOfImplicitConversion','U') IS NOT NULL
        DROP TABLE dbo.BewareOfImplicitConversion;

    CREATE TABLE dbo.BewareOfImplicitConversion
        ( RecordId                   INT         NOT NULL IDENTITY(100,10),
          RowDescription             VARCHAR(20) NOT NULL,
          IntMasqueradingAsCharacter VARCHAR(20)     NULL,
          IntValue                   INT             NULL
        );
END
GO

The column – dbo.BewareOfImplicitConversion.IntMasqueradingAsCharacter has been specifically named because we will be trying to insert numerical data into this column (which is supposed to hold character data) – it is perhaps one of the most common scenarios as far as integrating systems are concerned, however, we will be adding a twist – we will be inserting numeric data as-is, i.e. as integer values.

INSERT INTO dbo.BewareOfImplicitConversion (RowDescription, IntMasqueradingAsCharacter, IntValue)
VALUES ('One',   NULL, 1),
       ('Two',   NULL, 4),
       ('Three',    3, 6),
       ('Four',     4, 8),
       ('Five',   'V', 10);
GO

What we also did was to try and insert, in the same batch, one row of data that actually has character data in the column of interest. When executed, we end up in the following error:

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value ‘V’ to data type int.

However, when executed as shown below, the statements work just fine and insert all the 5 records into the table (dbo.BewareOfImplicitConversion).

INSERT INTO dbo.BewareOfImplicitConversion (RowDescription, IntMasqueradingAsCharacter, IntValue)
VALUES ('One',   NULL, 1),
       ('Two',   NULL, 4),
       ('Three',    3, 6),
       ('Four',     4, 8);

INSERT INTO dbo.BewareOfImplicitConversion (RowDescription, IntMasqueradingAsCharacter, IntValue)
VALUES ('Five',   'V', 10);
GO

SELECT * FROM dbo.BewareOfImplicitConversion;

Result:

Root Cause

The root cause here is very simple – before inserting data into the object, SQL Server has to first parse the queries populating the source data. During this process, the rules of data-type precedence come into the picture.

Data type precedence states that whenever an operator combines data from two expressions of different data types, the data-type with the lower precedence (in this case, it is the character data type – VARCHAR) is converted implicitly to a data-type with higher precedence (in this case – INT). When an implicit conversion fails, SQL Server will return an error.

In our example, the implicit conversion worked for the first 4 records being inserted because they did not require any implicit conversion, Implicit conversion is implemented for the data in the 5th record. Because they are part of the same statement, SQL Server will try to convert the string data implicitly to an integer value – causing the failure.

Solution/Best Practice:

Always use proper data-type casting and reference styles as shown in the query below and it is guaranteed to work.

INSERT INTO dbo.BewareOfImplicitConversion (RowDescription, IntMasqueradingAsCharacter, IntValue)
VALUES ('One',   NULL, 1),
       ('Two',   NULL, 4),
       ('Three',  '3', 6),
       ('Four',   '4', 8),
       ('Five',   'V', 10);
GO

Further Reading:

• Data Type Precedence [MSDN Link]

Until we meet next time,
Be courteous. Drive responsibly.