This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them. Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

SQL Server 2014's In-Memory Technology May Require Database Changes

Q: Is it true that SQL Server 2014's new In-Memory technology doesn't require any database changes? I've heard several Microsoft presentations where they say no database changes are required. Is this really true?

A: Like you might have guessed, the answer is, it depends on your database. To be clear, there are SQL Server 2014 data types and database features that are not supported by the new In-Memory OLTP engine. If your database uses these features, then you would need to change the schema of your database in order to get the new In-Memory OLTP features. If your database doesn't use any of these features, then you can indeed implement the new In-Memory OLTP without any schema changes.

Some of the common database features that are not compatible (not supported) with In-Memory OLTP are:

Database mirroring

AUTO_CLOSE database option

Computed columns

Triggers

FOREIGN KEY, CHECK, and UNIQUE constraints

FILESTREAM storage

ROWGUIDCOL

Clustered indexes

Memory-optimized tables support a maximum of eight indexes

COLUMNSTORE indexes

As you can see, there are a good number of SQL Server data types and features that aren't support by this initial release of In-Memory OLTP. If your databases use these features, then you'll need to change your schema in order to use In-Memory OLTP. You can find more information about the limitations and incompatibilities of SQL Server 2014's In-Memory OLTP engine at Transact-SQL Constructs Not Supported by In-Memory OLTP.