Microsoft SQL Server Natively compiled modules (Hekaton) Natively compiled stored procedure
Example
In a procedure with native compilation, T-SQL code is compiled to dll and executed as native C code. To create a Native Compiled stored Procedure you need to:
- Use standard CREATE PROCEDURE syntax
- Set NATIVE_COMPILATION option in stored procedure definition
- Use SCHEMABINDING option in stored procedure definition
- Define EXECUTE AS OWNER option in stored procedure definition
Instead of standard BEGIN END block, you need to use BEGIN ATOMIC block:
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE='us_english')
-- T-Sql code goes here
END
Example:
CREATE PROCEDURE usp_LoadMemOptTable (@maxRows INT, @FullName NVARCHAR(200))
WITH
NATIVE_COMPILATION,
SCHEMABINDING,
EXECUTE AS OWNER
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL=SNAPSHOT, LANGUAGE='us_english')
DECLARE @i INT = 1
WHILE @i <= @maxRows
BEGIN
INSERT INTO dbo.MemOptTable3 VALUES(@i, @FullName, GETDATE())
SET @i = @i+1
END
END
GO
