SQL Server 2014 – Memory Optimized Table (CTP1)


The new SQL Server 2014 CTP1 comes with a new cool feature called “memory optimized data”. This feature allow us to create a table in pretty much same manner as before with some different switches when defining the table file group and some options when the table is created. The feature is available from the Management Studio GUI, but wil open a new query windows as when we define a FileTable. But I guess this will be changed in future releases.

OLTP_Table

The below example shows how to create a database with a memory optimized filegroup.

sql-in-memory-create_db

The below example show how to create a table in the newly created memory optimized filegroup. This is done by the option “MEMORY_OPTIMIZED = ON”. A memory-optimized table can be defined with one of two DURABILITY values: SCHEMA_AND_DATA or SCHEMA_ONLY with the former being the default. A memory-optimized table defined with DURABILITY=SCHEMA_ONLY, which means that changes to the table’s data are not logged and the data in the table is not persisted on disk. This will increase performance dramatically.

sql-in-memory_create_table

Rember that NONCLUSTERED indexes are not available in CTP1.

Advertisements

One thought on “SQL Server 2014 – Memory Optimized Table (CTP1)

  1. Pingback: SQL Server 2014 CTP 2 available | Sveroa's Developer Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s