November 11, 2014 at 10:03 am
Just been reading up on this and there are a lot of limitation which one needs to consider when designing an in-memory table. I'm going to post them below in the hope that others will add to them so I can get a definitive list;
No foreign key constraints
No clustered indexes
No schema changes once the table is set in memory
No index changes once the table is set in memory
Alter Table function is not supported
Additional filegroup needs to be created in order to process in-memory tables
Varchar MAX is not supported
XML/User Defined data types are not supported
Max page length is 8060 (page overflow not supported)
Can't create indexes on NULLABLE columns
Wow, seems like a lot. Any more for any more?
--------------------------------------------
Laughing in the face of contention...
November 11, 2014 at 10:21 am
From the slides on the precon I did last week at Summit, this is just a list of table restrictions:
Restrictions
No LOB
No CLR
No user defined types
No VARIANT
No ROWVERSION
No foreign keys
Must have index
Durable tables must have a primary key
I wouldn't say that not having a clustered index is an issue. That's because the hash index, properly configured, is much faster. Additional restrictions include:
No cross-database queries
No more than 8 indexes.
There is a very narrow use case for the in-memory tables, primarily focused on OLTP operations and more specifically on those operations that are suffering from latch waits. If you are not in that situation, I really strongly don't recommend using in-memory tables.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 11, 2014 at 10:23 am
Some other issues include the fact that while the indexes on in-memory tables have statistics, you can't see the statistics information using DBCC SHOW_STATISTICS. Further, because of this, while the stats need to be updated, you can't tell when they're out of date in order to know when to update them.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply