Memory-optimized tables are used to store data in memory. All of the data in a memory-optimized tables resides in memory. Memory-optimized tables and disk-based tables can reside within the same database simultaneously.

A table is defined as being a memory-optimized table at the time of its creation. A memory-optimized table creation script is shown in the following code snippet:

CREATE TABLE dbo.Orders
(  
    OrderId   int not null IDENTITY  
        PRIMARY KEY NONCLUSTERED,  
    CustomerId int not null,  
    OrderDate  datetime not null,
    Quantity int not null  
)  
    WITH  
        (MEMORY_OPTIMIZED = ON,  
        DURABILITY = SCHEMA_AND_DATA);

The Memory_Optimized keyword specifies whether or not the table is a memory-optimized table. The durability refers to retaining only Schema or Schema and Data for the memory-optimized table. As the table is in memory, the data will go out of memory if a machine is restarted. However, if the durability is set to Schema_And_Data, SQL Server makes sure that the data isn't lost.

There are two types of indexes allowed on an in-memory table, and these are hash and non-clustered indexes. The indexes don't contain data rows. Instead, they contain memory pointers to the data rows. The indexes are also in memory.

Hash indexes are used to optimize point lookups and aren't suitable for range lookups. Non-clustered indexes are best suited for range lookups.

Memory-optimized tables can be accessed through the regular DDL and DML commands.

A regular or InterOP stored procedure is compiled and the plan is cached within the SQL server. However, a natively compiled procedure is compiled into a DLL and is loaded in memory. This further improves DML command performance on memory-optimized tables.

A natively compiled procedure syntax is given in the following figure:

Figure 9.1: Syntax of a Natively Compiled Procedure. This image has been sourced from https://www.microsoft.com/en-in/download/confirmation.aspx?id=41704

A natively compiled procedure contains the regular T-SQL code as the InterOP or regular procedures; however, it's defined differently at the time of creation.

The term Native_Compilation defines that the procedure is a natively compiled procedure and is to be compiled into DLL.

A natively compiled procedure should be schema-bounded and should have the execution context.

A natively compiled procedure is always executed in a snapshot transaction isolation level.

Memory-optimized tables and natively compiled procedures, which together can be used to speed up an OLTP workload and make it up to 20 times faster.

Clustered Columnstore indexes store the entire table data as column stores. They reduce the storage footprint by up to 10 times of its original size. They can be used on fact tables in a data warehouse to speed up the queries and fit more data into the available storage.

The syntax for creating a clustered column store index is as follows:

CREATE CLUSTERED COLUMNSTORE INDEX CCS_Orders ON [Sales].[Orders]

Non-clustered columnstore indexes are created on sets of tables columns and can co-exist. When introduced in SQL Server 2012, non-clustered column indexes weren't updatable; in other words, if you had a non-clustered column index on a table, you were not allowed to update the data in that table using the DML statements.

However, starting from SQL Server 2016, they are now updateable and can be used to gain real time operational insights on your transactional data. You can query the operational data directly instead of spending time doing ETL and loading the data into a data warehouse. You can do all of this without any operation impacts.

The syntax for creating a non-clustered column store index is as follows:

CREATE NONCLUSTERED COLUMNSTORE INDEX nccsix_CustomerID
ON [Sales].[Orders]  
(CustomerID,ContactPersonID,OrderDate);  

The preceding query creates a non-clustered column store index on customerid, contactpersonid, and orderdate. The column store structure is stored separately from the table structure.