Feeds:
Posts
Comments

Posts Tagged ‘SQL SERVER HEKATON – How to implement BLOB data type columns in the memory optimized table’

In my earlier article, I had discussed about how to implement identity column in the Memory optimized table. Now, lets try to create a BLOB data type column in memory optimized table.

Given below is the script that we usually use to implement BLOB data type columns in the disk based table (normal table). Lets try the same script for memory optimized table.

USE hkNorthwind
GO
CREATE TABLE tbl_sample
(
 [ID] integer not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Name] varchar(200) not null,
 [Address] varchar(Max)
) WITH (MEMORY_OPTIMIZED = ON)
GO
--OUTPUT

Msg 10770, Level 16, State 94, Line 3
The type ‘varchar(max)’ is not supported with memory optimized tables.

Oh no.. I am unable to create a BLOB data type column in memory optimized table and as we know, BLOB data types are one of the most commonly used data types in the database.

So what is the solution?

The solution is very simple. What you need to do is, actually split the above table into two tables and one as memory optimized table and 2nd one should be disk based table. And the logic behind this is, we will keep all the data types columns except BLOB in memory optimized table and will put all the BLOB data types column in disk based tables. But do not forget to keep the same ID in both tables.
Given below is the script.

USE hkNorthwind
GO
CREATE TABLE tbl_sample_Memory_table
(
 [ID] integer not null primary key nonclustered hash
 with (bucket_count = 1024),
 [Name] varchar(200) not null,
 ) WITH (MEMORY_OPTIMIZED = ON)
GO

CREATE TABLE tbl_sample_Normal_Table
(
 [ID] integer not null,
 [Address] varchar(Max)
)
GO
--OUTPUT

Command(s) completed successfully.

As you can see that, both tables are created successfully.

Note : You can download the hkNorthwind database from here.

Reference : MSDN

Read Full Post »