Creating Natively Compiled Stored Procedures

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Natively compiled stored procedures do not implement the full Transact-SQL programmability and query surface area. There are certain Transact-SQL constructs that cannot be used inside natively compiled stored procedures. For more information, see Supported Features for Natively Compiled T-SQL Modules.

The following Transact-SQL features are only supported for natively compiled stored procedures:

• Atomic blocks. For more information, see Atomic Blocks.

• NOT NULL constraints on parameters and variables. You cannot assign NULL values to parameters or variables declared as NOT NULL. For more information, see DECLARE @local_variable (Transact-SQL).

  • CREATE PROCEDURE dbo.myproc (@myVarchar VARCHAR(32) NOT NULL) AS (...)

  • DECLARE @myVarchar VARCHAR(32) NOT NULL = "Hello"; -- Must initialize to a value.

  • SET @myVarchar = NULL; -- Compiles, but fails during run time.

• Schema binding of natively compiled stored procedures.

Natively compiled stored procedures are created using CREATE PROCEDURE (Transact-SQL). The following example shows a memory-optimized table and a natively compiled stored procedure used for inserting rows into the table.

SQL

CREATE TABLE [dbo].[T2] (  
  [c1] [int] NOT NULL, 
  [c2] [datetime] NOT NULL,
  [c3] nvarchar(5) NOT NULL, 
  CONSTRAINT [PK_T1] PRIMARY KEY NONCLUSTERED ([c1])  
  ) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )  
GO  
  
CREATE PROCEDURE [dbo].[usp_2] (@c1 int, @c3 nvarchar(5)) 
WITH NATIVE_COMPILATION, SCHEMABINDING  
AS BEGIN ATOMIC WITH  
(  
 TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english'  
)  
  DECLARE @c2 datetime = GETDATE();  
  INSERT INTO [dbo].[T2] (c1, c2, c3) values (@c1, @c2, @c3);  
END  
GO  

In the code sample, NATIVE_COMPILATION indicates that this Transact-SQL stored procedure is a natively compiled stored procedure. The following options are required:

Option	Description
SCHEMABINDING	A natively compiled stored procedure must be bound to the schema of the objects it references. This means that tables referenced by the procedure cannot be dropped. Tables referenced in the procedure must include their schema name, and wildcards (*) are not allowed in queries (meaning no SELECT * from...). SCHEMABINDING is only supported for natively compiled stored procedures in this version of SQL Server.
BEGIN ATOMIC	The natively compiled stored procedure body must consist of exactly one atomic block. Atomic blocks guarantee atomic execution of the stored procedure. If the procedure is invoked outside the context of an active transaction, it will start a new transaction, which commits at the end of the atomic block. Atomic blocks in natively compiled stored procedures have two required options: TRANSACTION ISOLATION LEVEL. See Transaction Isolation Levels for Memory-Optimized Tables for supported isolation levels. LANGUAGE. The language for the stored procedure must be set to one of the available languages or language aliases.

See Also

Natively Compiled Stored Procedures