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
SQL Server Portal 