Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Implementing SQL_VARIANT in a Memory-Optimized Table

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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:
© 2015 Microsoft