Recently, I was doing research on memory optimized table and I found that most of the data types are supportive and few of them are non supportive as well. But even though few data data types are non supportive there are works around to deal with those data types as well. In my earlier article, I wrote about how to deal with BLOB data types even though they are non supportive. Rest of the non supportive data types work around, I will share in my upcoming posts.
Given below is the list of Supportive data types:
- bigint
- int
- tinyint
- smallint
- int
- real
- smallmoney
- money
- float
- decimal
- numeric
- bit
- uniqueidentifier
- smalldatetime
- datetime
- binary
- nchar
- sysname
Given below is the list of Non-supportive data types:
- image
- text
- sql_variant
- ntext
- varbinary(Max) (Limitation is 8060 bytes per row)
- varchar(Max) (Limitation is 8060 bytes per row)
- timestamp
- nvarchar(Max) (Limitation is 8060 bytes per row)
- xml
Let me know if you have work around for the non supportive data types.
SQL Server Portal 
Another possibility with varchar(max) and varbinary(max) is to create a non-memory optimized table and pin it into memory, if you need really fast access. However, there are lots of caveats with this approach. You won’t be able to compile native C access like a real in-memory table. It will use loads of RAM, and so it may not be a good idea to do it unless you have obscenely large memory on your server. This sort of table will have the same sort of performance as an ordinary SQL Server 2012 pinned table, and not the ultra advanced performance of a Hekaton table.