Introduction
Natively Compiled Stored Procedures are T-SQL procedures compiled to native code and access memory optimized tables. They allow for the efficient execution of business logic and T-SQL queries within the procedure. Stored Procedures marked as NATIVE_COMPILATION are Natively Compiled Stored Procedures. Natively Compiled Stored Procedures are compiled when they are created. This is the main difference between Natively Compiled Stored Procedures and disk-based (interpreted) Stored Procedure. Disk-based (interpreted) Stored Procedures are compiled when called the first time.
Natively Compiled Stored Procedures are able to identify error conditions like arithmetic overflow, type conversion, and divide-by-zero conditions when they are created. If these errors are present in code, it is not created. A disk-based (interpreted) Stored Procedure are not created due to these errors, they will throw runtime errors. Native compilation allows for faster and more efficient data access than interpreted (traditional or disk based) Transact-SQL.
Examples
The following are examples.
CREATE TABLE TestTable
(
Id INT NOT NULL PRIMARY KEY NONCLUSTERED
HASH WITH (BUCKET_COUNT = 50000),
StartDate DATETIME NOT NULL,
Code VARCHAR(10) NOT NULL,
)
WITH (MEMORY_OPTIMIZED = ON)
GO
CREATE PROCEDURE TestProc
(
@Id INT,
@Code VARCHAR(10)
)
WITH NATIVE_COMPILATION, SCHEMABINDING,
EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH
(
TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'English'
)
INSERT INTO TestTable (Id, StartDate, Code)
VALUES (@Id, @Code, GETDATE())
END
SCHEMABINDING: SCHEMABINDING is only supported in SQL Server version 2014. Natively Compiled Stored Procedure must be bound to the schema of the objects used in the procedure. So tables cannot be dropped that are referenced by the procedure.
EXECUTE AS: A Natively Compiled Stored Procedure does not support EXECUTE AS CALLER (default execution context). So it is mandatory to specify the execution context when it is created. EXECUTE AS OWNER, EXECUTE AS [user] and EXECUTE AS SELF are supported.
BEGIN ATOMIC: The Natively Compiled Stored Procedure body must be consisting of exactly one atomic block. This will ensure, if the Stored Procedure is invoked outside the active transaction context, that it will start a new transaction. An ATOMIC block must have two required options TRANSACTION ISOLATION LEVEL and LANGUAGE.
A Natively Compiled Stored Procedure does not support all T-SQL programmability. There are certain T-SQL statements, those that cannot be used within Natively Compiled Stored Procedures.
The following programmability is supported by Natively Compiled Stored Procedures.
- IF and WHILE is supported with Natively Compiled Stored Procedure.
- Sub Query is not supported.
- With WHERE and HAVING clause, operator “AND” and “BETWEEN” are supported but OR, NOT and IN are not supported.
- TRY / CATCH / THROW is supported with Natively Compiled Stored Procedure.
- SELECT, SET and RETURN are supported with Natively Compiled Stored Procedure.
- We can use Memory-optimized table types and table variables with Natively Compiled Stored Procedure.
- We can declare variable as NULL or NOT NULL. If a variable is declared as NOT NULL then the declaration must have an initializer. If a variable is declared as NULL then an initializer is optional.
The following expressions are supported by Natively Compiled Stored Procedures.
- Logical AND, OR and NOT (OR and NOT operator is not supported with WHERE and HAVING clause).
- Comparison Operators: =, <>, > , < , >= and <=
- Binary Operators: +,-, * and /
The following built-in functions are supported by Natively Compiled Stored Procedures:
- Conversion function: CAST and CONVERT (Note: Conversions between Unicode (n(var)char) and non-Unicode character strings ((var)char) are not supported.
- String functions: LTRIM, RTRIM, SUBSTRING and LEN
- Date functions: DATEADD, DATEDIFF, DATEFROMPARTS, DATEPART, SMALLDATETIMEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, CURRENT_TIMESTAMP, DAY, GETDATE, GETUTCDATE, MONTH, EOMONTH, YEAR, SYSDATETIME and SYSUTCDATETIME
- Unique Identifier functions : NEWID and NEWSEQUENTIALID
- Error functions: ERROR_MESSAGE, ERROR_NUMBER, ERROR_LINE,ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE
- Math functions: SIN, ASIN, COS, ACOS, TAN, ATAN, ATN2, COT, DEGREES, EXP, LOG, LOG10, PI, POWER, SQRT, SQUARE and RAND
- Other functions: SCOPE_IDENTITY and ISNULL and @@ROWCOUNT
The following Query Statements are supported with Natively Compiled Stored Procedures.
- The keywords BETWEEN, IS NULL and IS NOT NULL are supported in Natively Compiled Stored Procedures.
- INNER JOIN, CROSS JOIN, FORCE ORDER and INNER LOOP JOIN query hints are supported with Natively Compiled Stored Procedure.
- The keywords SELECT, UPDATE and DELETE are supported with Natively Compiled Stored Procedures.
- FROM [memory optimized table] and ORDER BY clauses are supported.
- GROUP BY is supported with the aggregate functions AVG, COUNT, COUNT_BIG, MIN, MAX, and SUM. MIN and MAX are not supported with type nvarchar, char, varchar, varchar, varbinary, and binary.
- INSERT VALUES and INSERT SELECT statement are supported.
- INDEX and FORCESEEK hints (both table hints and OPTION clause of the query) are supported.
- ORDER BY and TOP are supported with some limitation like DISTINCT is not supported with SELECT or ORDER BY clause, WITH TIES or PERCENT is not supported with TOP clause.
- ORDER BY combine with TOP does not supported if result set have more than 8192 rows.
- If result set is more than 8192 rows, it throws the error message:
Msg 41398, Level 16, State 1, Procedure testProc, Line 5 The TOP operator can return a maximum of 8192 rows; 9000 was requested.
- Column name aliases are supported
- HAVING clause is supported with some limitation like WHERE clause.
Conclusion
In memory Online Transaction Processing (OLTP) introduced the concept of native compilation. Native compilation allows efficient query execution and faster data access than the disk base (traditional) T-SQL. Native compilation Stored Procedure produce DLLs.