在内存优化的表中实现 SQL_VARIANT
适用于： 
SQL Server Azure SQL 数据库 Azure SQL 托管实例
考虑具有 SQL_VARIANT 列的表的示例：
CREATE TABLE [dbo].[T1]([Key] [sql_variant] NOT NULL)
假定键列只能是 BIGINT 或 NVARCHAR(300)。 您可以按以下方式对此表建模：
-- original disk-based table
CREATE TABLE [dbo].[T1_disk]([Key] int not null primary key,
[Value] [sql_variant])
go
insert dbo.T1_disk values (1, 12345678)
insert dbo.T1_disk values (2, N'my nvarchar')
insert dbo.T1_disk values (3, NULL)
go
-- new memory-optimized table
CREATE TABLE [dbo].[T1_inmem]([Key] INT NOT NULL PRIMARY KEY NONCLUSTERED,
[Value_bi] BIGINT,
[Value_nv] NVARCHAR(300),
[Value_enum] TINYINT NOT NULL) WITH (MEMORY_OPTIMIZED=ON)
go
-- copy data
INSERT INTO dbo.T1_inmem
SELECT [Key],
CASE WHEN SQL_VARIANT_PROPERTY([Value], 'basetype') = 'bigint' THEN convert (bigint, [Value])
ELSE NULL END,
CASE WHEN SQL_VARIANT_PROPERTY([Value], 'basetype') != 'bigint' THEN convert (nvarchar(300), [Value])
ELSE NULL END,
CASE WHEN SQL_VARIANT_PROPERTY([Value], 'basetype') = 'bigint' THEN 1
ELSE 0 END
FROM dbo.T1_disk
GO
-- select data, converting back to sql_variant [will not work inside native proc]
select [Key],
case [Value_enum] when 1 then convert(sql_variant, [Value_bi])
else convert(sql_variant, [Value_nv])
end
from dbo.T1_inmem
现在您可以通过在 T1 上打开游标，将数据加载到 T1 的 [T1_HK]：
DECLARE T1_rows_cursor CURSOR FOR
select *
FROM dbo.T1
OPEN T1_rows_cursor
-- declare 1 variable each for column in HK table
Declare
@Key_biBIGINT = 0,
@Key_nvnvarchar(300)= ' ',
@Key_enumsmallint,
@Keysql_variant
FETCH NEXT FROM T1_rows_cursor INTO @key
WHILE @@FETCH_STATUS = 0
BEGIN
-- setting the input parameters for inserting into the memory-optimized table
-- convert SQL Variant types
-- @key_enum =1 represents BIGINT
if (SQL_VARIANT_PROPERTY(@Key, 'basetype') = 'bigint')
begin
set @key_bi = convert (bigint, @Key)
set @key_enum = 1
set @key_nv = 'invalid'
end
else
begin
set @Key_nv = convert (nvarchar (300), @Key)
set @Key_enum = 0
set @Key_bi = -1
end
-- inserting the row
INSERT INTO T1_HK VALUES (@Key_bi, @Key_nv, @Key_enum)
FETCH NEXT FROM T1_rows_cursor INTO @key
END
CLOSE T1_rows_cursor
DEALLOCATE T1_rows_cursor
你可以按以下方式将数据转换回 SQL_VARIANT ：
case [Key_enum] when 1 then convert(sql_variant, [Key_bi])
else convert(sql_variant, [Key_nv])
end