Quick Notes on InMemory Tables:
1. Inmemory table cannot be truncated.
2. Identity value cannot be reseeded for InMemory table columns.
3. Indexed view cannot be created on Inmemory table (Views can be created on InMemory tables).
4. DBCC CheckTable does not work on InMemory Table. DBCC CheckDB skips inmemory tables.
5. There is no need to do index defragmentation for Inmemory table indexes.
(08/03/2016)Update: Few more pointers on InMemory Tables:
- Identity value cannot be increment by more than 1 value.
- InMemory Tables cannot be used in cross-database transactions. However, Inmemory table variables can be used for cross-database transactions.
Example: The below query does not work for InMemory Table
Use TargetDB
Insert into MyInMemoryTable
Select * from SourceDB.dbo.MyDiskBasedTable
However, we can define the inmemory table variable(create a Inmemory Data Type and the table variable is created with this data type) and can use it.
Use TargetDB
Create Type MyInMemoryDataType as Table (<<list the columns>>) as (memory_Optimized=ON)
declare InMemoryTableVariable MyInMemoryDataType
Insert into InMemoryTableVariable
Select * from SourceDB.dbo.MyDiskBasedTable
SQL Server Repository 