Implementando SQL_VARIANT em uma tabela com otimização de memória
Considere um exemplo de uma tabela com a coluna SQL_VARIANT:
CREATE TABLE [dbo].[T1]([Key] [sql_variant] NOT NULL)
Suponha que a coluna de chave possa ser apenas um BIGINT ou NVARCHAR(300). Você pode modelar essa tabela como se segue:
-- 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
Agora, você pode carregar dados em [T1_HK] de T1 abrindo um cursor em T1:
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
Você pode converter dados de volta para SQL_VARIANT como se segue:
case [Key_enum] when 1 then convert(sql_variant, [Key_bi])
else convert(sql_variant, [Key_nv])
end