Search code examples
sqlsql-servert-sqlsql-insertunion-all

SQL Server, T-SQL: weird behavior on "multiple value INSERT INTO statement" + sql_variant type column

Writing a script to experiment with sql_variant data type and SQL_VARIANT_PROPERTY function (to recover data information from a sql_variant column) on SQL Server I discover, accidentally, what I consider an unexpected behavior on a multiple value INSERT INTO statement.

The following script illustrates the behavior:

BEGIN
    DECLARE @TblVariant AS TABLE (
         rowid int identity
        ,VariantValue sql_variant
        ,AsStringColumn varchar(max)
        ,AsStringTypeInfo varchar(max)
        ,AsStringValue varchar(max)
        ,Result varchar(max)
    )

    --Inserting multiple values with one INSERT INTO statement
    INSERT INTO @TblVariant (VariantValue)
    VALUES (convert(float        ,10.25))
          ,(convert(int          ,11.00))
          ,(convert(numeric(10,0),12.15))
          ,(convert(numeric(10,2),13.50))
          ,(convert(bigint       ,14.75))
          ,(null)

    UPDATE @TblVariant 
    SET AsStringColumn = convert(varchar(max),VariantValue)
          ,AsStringTypeInfo = 
                 'BaseType='  +isnull(try_convert(varchar(max),SQL_VARIANT_PROPERTY(VariantValue,'BaseType')),'NULL')
               +';Precision=' +isnull(try_convert(varchar(max),SQL_VARIANT_PROPERTY(VariantValue,'Precision')),'NULL')
               +';Scale='     +isnull(try_convert(varchar(max),SQL_VARIANT_PROPERTY(VariantValue,'Scale')),'NULL')
               +';TotalBytes='+isnull(try_convert(varchar(max),SQL_VARIANT_PROPERTY(VariantValue,'TotalBytes')),'NULL')
               +';Collation=' +isnull(try_convert(varchar(max),SQL_VARIANT_PROPERTY(VariantValue,'Collation')),'NULL')
               +';MaxLength=' +isnull(try_convert(varchar(max),SQL_VARIANT_PROPERTY(VariantValue,'MaxLength')),'NULL')

    SELECT *
    FROM @TblVariant

    DELETE @TblVariant

    -- Multiple insert statements 
    INSERT INTO @TblVariant (VariantValue) VALUES (convert(float, 10.25))
    INSERT INTO @TblVariant (VariantValue) VALUES (convert(int, 11.00))
    INSERT INTO @TblVariant (VariantValue) VALUES (convert(numeric(10, 0), 12.15))
    INSERT INTO @TblVariant (VariantValue) VALUES (convert(numeric(10, 2), 13.50))
    INSERT INTO @TblVariant (VariantValue) VALUES (convert(bigint, 14.75))
    INSERT INTO @TblVariant (VariantValue) VALUES (NULL)

    UPDATE @TblVariant 
    SET AsStringColumn = convert(varchar(max),VariantValue)
          --Ojo a este comportamiento. Al 
          ,AsStringTypeInfo = 
                 'BaseType='  +isnull(try_convert(varchar(max),SQL_VARIANT_PROPERTY(VariantValue,'BaseType')),'NULL')
               +';Precision=' +isnull(try_convert(varchar(max),SQL_VARIANT_PROPERTY(VariantValue,'Precision')),'NULL')
               +';Scale='     +isnull(try_convert(varchar(max),SQL_VARIANT_PROPERTY(VariantValue,'Scale')),'NULL')
               +';TotalBytes='+isnull(try_convert(varchar(max),SQL_VARIANT_PROPERTY(VariantValue,'TotalBytes')),'NULL')
               +';Collation=' +isnull(try_convert(varchar(max),SQL_VARIANT_PROPERTY(VariantValue,'Collation')),'NULL')
               +';MaxLength=' +isnull(try_convert(varchar(max),SQL_VARIANT_PROPERTY(VariantValue,'MaxLength')),'NULL')

    SELECT *
    FROM @TblVariant
END

The script above uses a table variable @TblVariant with a sql_variant column named VariantValue to store some data, inserts some values into @TblVariant and then uses SQL_VARIANT_PROPERTY to recover data type info from the column VariantValue.

The first scenario inserts the multiple values using one INSERT INTO statement, after data deletion from @TblVariant, the second scenario repeats the process using multiple INSERT INTO statements to repopulate @TblVariant with the same data

The result looks as follows

enter image description here

As we see, in both scenarios the values in VariantValue and AsStringColumn look equal for all rows, but the calculated column AsStringTypeInfo looks different on each scenario.

For the one INSERT INTO statement scenario, the results for almost all rows (except for the row with null on VariantValue column) are the same; whilst on the other hand for the multiple INSERT INTO statements scenario, the value calculated for AsStringTypeInfo look different for each row providing more accurate info for the value inserted / stored on the VariantValue column, for each row.

My question is: what is the difference between to perform one INSERT INTO statement to insert multiple row values and to perform multiple INSERT INTO statements to insert the same values, and why that difference affects the SQL_VARIANT_PROPERTY function behavior ?

Solution

  • As answered by @lptr in comments:

    Values() is a table value constructor. Each column of values() (since it is a table constructor) must be of the same single datatype.

    The first, multiple, values statement has multiple datatypes, so the one with the highest precedence is float. The values outputs float which is converted to sql_variant when inserted into the table. In short, values() performs a union all of each row (from the execution plan, constant scan, <OutputList><ColumnReference Column="Union1009">

    You could cast/convert one of the values to sql_variant, then sql_variant is of the highest precedence

    INSERT INTO @TblVariant (VariantValue) VALUES
(convert(sql_variant, convert(float ,10.25))),
(convert(int,11.00)),
(convert(numeric(10,0),12.15))