Export (0) Print
Expand All

Implementing SQL_VARIANT in a Memory-Optimized Table

SQL Server 2014

Consider an example of a table with SQL_VARIANT column:

CREATE TABLE [dbo].[T1](
  [Key] [sql_variant] NOT NULL
)

Assume that the key column can only be either a BIGINT or NVARCHAR(300). You can model this table as follows:

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

Now you can load data into [T1_HK] from T1 by opening a cursor on 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

You can convert data back to SQL_VARIANT as follows:

case [Key_enum] when 1 then convert(sql_variant, [Key_bi]) 
                       else convert(sql_variant, [Key_nv]) 
                       end

Community Additions

ADD
Show:
© 2014 Microsoft