Q  A

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.

Related: Rev Up Application Performance with the In-Memory OLTP Engine

Microsoft's Analyze, Migrate, and Report (AMR) tool can help you to analyze your databases for any incompatibilities with the new In-Memory OLTP feature. Some of the data types that are not supported by the In-Memory OLTP feature include:

  • Datetimeoffset
  • Geography
  • Hierarchyid
  • Image
  • Ntext
  • Sql_variant
  • Text
  • Varchar(max)
  • Xml
  • User data types (UDTs)

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
  • 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.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.