英文:
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
。
结果如下:
如图所示,在两个场景中,VariantValue
和 AsStringColumn
列的值对于所有行都相同,但计算的 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
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()
的每一列(因为它是一个表构造函数)必须是相同的单一数据类型。
首个多值语句包含多个数据类型,所以具有最高优先级的是float
。values
输出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))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论