Natively compiled stored procedures

Natively compiled stored procedures are compiled when created and bypass the query execution engine. The procedure is compiled when created, and also manually or when the database or server are restarted.

A few additional concepts are introduced here, including SCHEMABINDING and BEGIN ATOMIC, both of which are required for natively compiled stored procedures.

SCHEMABINDING locks the table definition to prevent alteration after the stored procedure is created. SCHEMABINDING allows the compiled stored procedure to be certain of the data types involved. The tables involved in the natively compiled stored procedure cannot be altered without dropping the SCHEMABINDING, making changes and then reapplying the SCHEMABINDING. SHEMABINDING also requires that explicit field names are used in the query; "select *…" will not work.

BEGIN ATOMIC is required in a natively compiled stored procedure and is only available for a natively compiled stored procedure. In interactive (non-natively compiled) procedures, you would use a BEGIN TRAN statement block. Using the ATOMIC block and transaction settings will be independent of the current connection/settings as the stored procedure may be used in different execution sessions.

If there is an existing active transaction, BEGIN ATOMIC will set a save point and roll back to that if it fails. Otherwise, a new transaction is created and completed or rolled back.

You indicated a natively compiled stored procedure in the create declaration of the stored procedure using the "NATIVE_COMPILATION" directive.

In the following sample, we will create a memory-optimized table and a natively stored procedure. Note that memory-optimized tables cannot have clustered indexes. Memory-optimized tables are stored as rows, not in pages, as with a disk-based table:

-- Create Memory-Optimized Table
USE MemOptDB;
GO
CREATE TABLE dbo.MyMemOptTable
(
  id int not null,
  dtCreated datetime not null,
  orderID nvarchar(10) not null
  CONSTRAINT pk_id PRIMARY KEY NONCLUSTERED (id)
  )
  WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO
-- Create Natively Stored Procedure
CREATE PROCEDURE dbo.myNativeProcedure (@id int)
  WITH NATIVE_COMPILATION, SCHEMABINDING
  AS BEGIN ATOMIC WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english' )
  SELECT id, dtCreated, orderID
  FROM dbo.MyMemOptTable
  WHERE id = @id
  END
GO

The table schema is locked due to the reference to a natively compiled stored procedure. If you try to alter the table, an exception will be thrown, as shown here:

-- Try to alter the schema!
ALTER TABLE [dbo].[MyMemOpttable]
  ALTER COLUMN orderId nvarchar(20)
GO
Msg 5074, Level 16, State 1, Line 55
The object 'myNativeProcedure' is dependent on column 'orderId'.
Msg 4922, Level 16, State 9, Line 55
ALTER TABLE ALTER COLUMN orderId failed because one or more objects access this column.

More information on natively compiled procedures can be found here:

https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/creating-natively-compiled-stored-procedures?view=sql-server-2017.