Recently, a reader asked me if it is possible to recompile InMemory objects in SQL Server. This question got me curious, so I decided to explore the possibility in SQL Server 2016. In this article, I will share the steps I took to recompile a Native InMemory stored procedure.

Step 1: Create a Database

First, we need to create a database with a filegroup for InMemory OLTP. Here is the code:

USE MASTER;
GO

IF DB_ID('InMem_OLTP') IS NOT NULL
BEGIN
    ALTER DATABASE [InMem_OLTP] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [InMem_OLTP];
END

CREATE DATABASE [InMem_OLTP] ON PRIMARY
(
    NAME = N'InMem_OLTP_data',
    FILENAME = N'C:\DATA\InMem_OLTP_data.mdf',
    SIZE = 50MB
)
LOG ON
(
    NAME = N'InMem_OLTP_log',
    FILENAME = N'C:\DATA\InMem_OLTP_log.ldf',
    SIZE = 10MB
);
GO

USE InMem_OLTP;
GO

ALTER DATABASE InMem_OLTP ADD Filegroup [InMem_OLTP_InMemory] CONTAINS MEMORY_OPTIMIZED_DATA;
GO

ALTER DATABASE InMem_OLTP ADD FILE
(
    NAME = InMem_OLTP_InMemory,
    FILENAME = N'C:\Data\InMem_OLTP_mopt'
) TO FILEGROUP [InMem_OLTP_InMemory];

Step 2: Create an InMemory Table

Next, we need to create an InMemory table. Here is the code:

CREATE TABLE dbo.SalesOrder_inmem
(
    order_id INT IDENTITY NOT NULL,
    order_date DATETIME NOT NULL,
    order_status TINYINT NOT NULL,
    amount FLOAT NOT NULL,
    CONSTRAINT PK_SalesOrderID PRIMARY KEY NONCLUSTERED HASH (order_id) WITH (BUCKET_COUNT = 10000)
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO

Step 3: Create a Native Stored Procedure

Now, let’s create a Native stored procedure for our experiment. Here is the code:

USE InMem_OLTP;
GO

CREATE PROCEDURE [dbo].[InsertSalesOrder_Native]
    @order_status TINYINT = 1,
    @amount FLOAT = 100,
    @order_count INT = 100
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'us_english')
    DECLARE @i INT = 0;

    WHILE @i < @order_count
    BEGIN
        INSERT INTO dbo.SalesOrder_inmem VALUES (GETDATE(), @order_status, @amount);
        SET @i = @i + 1;
    END
END
GO

Step 4: Recompile the Stored Procedure

Finally, let’s recompile the stored procedure using the sp_recompile system stored procedure. Here is the code:

EXEC sp_recompile 'InsertSalesOrder_Native';

The output for the above code should be: “Object ‘InsertSalesOrder_Native’ was successfully marked for recompilation.”

As you can see, we were able to successfully recompile the stored procedure without any problem. This experiment was conducted on a SQL Server 2016 environment. If you have ever needed this capability in your environment, I would love to know some of the use cases for it.