Natively Compiled Procedures: ATOMIC Blocks and T-SQL Limits

ATOMIC blocks are what turn a stored procedure into a natively compiled one. The block sets the transaction and the settings, and it brings a short list of T-SQL you cannot use. Learn the limits on a tiny procedure first.

Why native procedures feel strict

Picture a colleague who moves a hot table into memory and pastes a favorite stored procedure into a native one. The procedure has a temp table, a BEGIN TRANSACTION, and a RAISERROR. The result is a wall of red text.

Nothing is wrong with the colleague. A natively compiled procedure is built ahead of time, so SQL Server checks every statement when you create it. That is a good thing. Problems show up at deployment, not at 2 AM. This post builds a small one and pushes on its limits.

Build a memory-optimized table first

A native procedure works on memory-optimized tables, so we need one. That takes a filegroup for memory-optimized data and a container folder. The demo uses the instance’s default data folder, so you can run it as is. It creates a database called SqlAuthorityDemo and drops it at the end.

The table has a nonclustered primary key, which memory-optimized tables accept, and SCHEMA_AND_DATA durability, so rows survive a restart. The last query confirms that your server supports In-Memory OLTP. It should return 1.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;
GO
CREATE DATABASE SqlAuthorityDemo;
GO
ALTER DATABASE SqlAuthorityDemo
    ADD FILEGROUP MemoryData CONTAINS MEMORY_OPTIMIZED_DATA;
GO
DECLARE @Folder nvarchar(300) =
    CONVERT(nvarchar(300), SERVERPROPERTY('InstanceDefaultDataPath'));
DECLARE @Sql nvarchar(max) = N'ALTER DATABASE SqlAuthorityDemo
    ADD FILE (NAME = N''MemoryContainer'',
              FILENAME = N''' + @Folder + N'SqlAuthorityDemo_memory'')
    TO FILEGROUP MemoryData;';
EXEC (@Sql);
GO
USE SqlAuthorityDemo;
GO
CREATE TABLE dbo.MemoryItems
(
    id         int           NOT NULL PRIMARY KEY NONCLUSTERED,
    value_text nvarchar(80)  NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

SELECT SERVERPROPERTY('IsXTPSupported') AS in_memory_oltp_supported;

Write the procedure with an ATOMIC block

Now the procedure. Two things in the header matter: NATIVE_COMPILATION and SCHEMABINDING. Inside, BEGIN ATOMIC needs an isolation level and a language. The block is the transaction, so the procedure either does all of its work or none of it.

The procedure rejects zero or negative ids with THROW. THROW is allowed in a native procedure. A valid call stores row 1 with the value Sample. A call with id 0 stops with error 50000 and the message “Choose a positive identifier.”

CREATE PROCEDURE dbo.AddMemoryItem @id int, @value nvarchar(80)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    IF @id <= 0 THROW 50000, N'Choose a positive identifier.', 1;
    INSERT dbo.MemoryItems (id, value_text) VALUES (@id, @value);
END;
GO
EXEC dbo.AddMemoryItem @id = 1, @value = N'Sample';
SELECT id, value_text FROM dbo.MemoryItems ORDER BY id;

BEGIN TRY
    EXEC dbo.AddMemoryItem @id = 0, @value = N'Rejected';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS validation_error;
END CATCH;

See what native compilation refuses

Now the colleague’s habits. Each procedure below breaks one rule, and each fails when you create it. A temp table fails with error 10794. So does BEGIN TRANSACTION, because the block already is the transaction. RAISERROR also fails with 10794, so use THROW. Dynamic SQL with EXEC and a string fails with error 12340.

Read the messages. They name the statement SQL Server did not accept. If you need a temp table or dynamic SQL, keep that work in a normal procedure. Let it call the native one with plain parameters.

CREATE PROCEDURE dbo.TryTempTable
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    CREATE TABLE #Work (id int);
END;
GO
CREATE PROCEDURE dbo.TryOwnTransaction
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    BEGIN TRANSACTION;
END;
GO
CREATE PROCEDURE dbo.TryRaiserror
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    RAISERROR (N'Something went wrong.', 16, 1);
END;
GO
CREATE PROCEDURE dbo.TryDynamicSql
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    EXEC (N'SELECT 1');
END;

Let the block be the transaction

Here is the proof that the block works as one unit. This procedure inserts two rows. The second one points at id 1, which already exists. The call fails with error 2627, and the first row, id 10, is not there afterwards. Both inserts were undone together.

A second call with free ids, 10 and 11, stores both rows. I did not time any of this. Native compilation is meant for short, hot transactions, so test conflicts, retries, and real speed on your own workload before you promise anyone a gain.

CREATE PROCEDURE dbo.AddTwoItems @first int, @second int
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    INSERT dbo.MemoryItems (id, value_text) VALUES (@first, N'First of a pair');
    INSERT dbo.MemoryItems (id, value_text) VALUES (@second, N'Second of a pair');
END;
GO
BEGIN TRY
    EXEC dbo.AddTwoItems @first = 10, @second = 1;   -- id 1 already exists
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS error_number;
END CATCH;

SELECT id, value_text FROM dbo.MemoryItems ORDER BY id;

EXEC dbo.AddTwoItems @first = 10, @second = 11;
SELECT id, value_text FROM dbo.MemoryItems ORDER BY id;

The last block drops the demo database, and the memory-optimized files go with it.

USE master;
DROP DATABASE IF EXISTS SqlAuthorityDemo;

Start with one short transaction, and grow it only when the limits stop surprising you.

Native compilation is not a universal accelerator, it is a focused transaction design.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.