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