Introduction to In-Memory OLTP in SQL Server


In-Memory OLTP is a powerful feature in SQL Server that can significantly boost the performance of your database applications. In this introduction, we'll explore the basics of In-Memory OLTP and provide sample code snippets to get you started.


What is In-Memory OLTP?

In-Memory OLTP, also known as Hekaton, is a feature in SQL Server that allows you to store and process data entirely in memory. It's designed for high-performance, low-latency, and heavily concurrent workloads. In-Memory OLTP uses memory-optimized tables and natively compiled stored procedures to achieve its performance benefits.


Key Benefits of In-Memory OLTP

There are several advantages to using In-Memory OLTP:


  • Improved Performance: In-Memory OLTP can provide significantly faster data access and processing, making it ideal for demanding applications.
  • Reduced Locking and Blocking: With optimistic concurrency control, it reduces contention and allows for higher concurrency.
  • Memory-Optimized Tables: These tables are designed for speed, and they can be ideal for specific use cases.

Sample Code for Creating In-Memory Tables

Here's an example of creating an In-Memory table in SQL Server:


-- Create an In-Memory table
CREATE TABLE dbo.MyInMemoryTable
(
    ID INT NOT NULL PRIMARY KEY NONCLUSTERED,
    Name NVARCHAR(50) NOT NULL
) WITH (MEMORY_OPTIMIZED = ON);

Working with Natively Compiled Stored Procedures

In-Memory OLTP also allows you to create natively compiled stored procedures, which are optimized for high-performance. Here's an example:


-- Create a natively compiled stored procedure
CREATE PROCEDURE dbo.InsertIntoInMemoryTable
    @ID INT,
    @Name NVARCHAR(50)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    INSERT INTO dbo.MyInMemoryTable (ID, Name)
    VALUES (@ID, @Name);
END;

What's Next?

As you become more familiar with In-Memory OLTP, you can explore advanced topics like performance optimization, tuning, and understanding when to use this feature effectively in your database applications.