SQL Server, T-SQL: “multiple value INSERT INTO statement” + sql_variant type column 的行为奇怪

huangapple go评论65阅读模式
英文:

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

问题

这个脚本使用了一个名为 @TblVariant 的表变量,其中包含一个 sql_variant 列名为 VariantValue 以存储一些数据,然后将一些值插入 @TblVariant,并使用 SQL_VARIANT_PROPERTY 从列 VariantValue 中恢复数据类型信息。

第一个场景使用一个 INSERT INTO 语句插入多个值,然后从 @TblVariant 删除数据,第二个场景使用多个 INSERT INTO 语句重复相同的过程,以再次填充 @TblVariant

结果如下:

SQL Server, T-SQL: “multiple value INSERT INTO statement” + sql_variant type column 的行为奇怪

如图所示,在两个场景中,VariantValueAsStringColumn 列的值对于所有行都相同,但计算的 AsStringTypeInfo 列在每个场景中都不同。

对于一个 INSERT INTO 语句场景,几乎所有行的结果(除了 VariantValue 列为空的行)都相同;而对于多个 INSERT INTO 语句场景,为每行计算的 AsStringTypeInfo 值都不同,为 VariantValue 列中插入/存储的值提供了更准确的信息。

我的问题是:执行一个 INSERT INTO 语句来插入多行值与执行多个 INSERT INTO 语句来插入相同的值之间有什么区别,以及为什么这种差异会影响 SQL_VARIANT_PROPERTY 函数的行为?

英文:

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

SQL Server, T-SQL: “multiple value INSERT INTO statement” + sql_variant type column 的行为奇怪

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 ?

答案1

得分: 2

Values()是一个表值构造函数values()的每一列(因为它是一个表构造函数)必须是相同的单一数据类型。

首个多值语句包含多个数据类型,所以具有最高优先级的是floatvalues输出float,当插入表时会转换为sql_variant。简而言之,values()执行每一行的union all操作(从执行计划中看,是constant scan<OutputList><ColumnReference Column="Union1009">)。

您可以将其中一个值转换为sql_variant,然后sql_variant将具有最高的优先级。

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

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

huangapple
  • 本文由 发表于 2023年3月31日 23:41:02
  • 转载请务必保留本文链接:https://go.coder-hub.com/75900385.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定