Introduction
The Natively Compiled Stored Procedure is Introduced in SQL Server 2014. They are compiled when they are created. Native compilation allows for faster and more efficient data access than interpreted or traditional or disk based Transact-SQL.
A Natively Compiled Stored Procedure does not support all T-SQL programmability. There are many features, functions and keywords that are not available in a natively compiled Stored Procedure.
Features not available with Natively Compiled Stored Procedures
| Feature |
Remark or alternative approach |
| Cursors |
Cursors are not supported in natively compiled Stored Procedures. Alternatively we can use a WHILE loop instead of Cursors |
| Sub query |
Sub queries are not supported.
We can use a join instead of a sub query if possible. |
| SELECT INTO |
INTO Clause is not supported with a SELECT keyword.
Alternatively we can use INSERT INTO [TableName] SELECT.
Example:
SELECT Id, Name into table2 from table1 – Not supported
INSERT INTO table2
SELECT Id, Name from Table1
Note: In an INSERT statement, values must be specified for all columns. |
| CLR Store procedure |
A CLR Stored Procedure cannot be natively compiled. |
| CTE (common table Expression) |
Common Table Expressions are not supported in natively compiled Stored Procedures. |
| MARS |
Multiple Active Result Set is not supported with natively compiled Stored Procedures. |
| Linked servers |
Linked servers are not supported with natively compiled Stored Procedure. |
| Temporary table |
Temporary table cannot be used in natively compiled Stored Procedures. Instead of a temporary table we can use a memory-optimized table with DURABILITY=SCHEMA_ONLY. |
| DTC |
Natively compiled Stored Procedures and memory-optimized tables cannot be accessed from distributed transactions. |
| OUTER JOIN |
OUTER JOIN is not supported with a natively compiled Stored Procedure. |
| PIVOT / UNPIVOT |
PIVOT / UNPIVOT is not supported with a natively compiled Stored Procedure. |
| APPLY |
The operator APPLY is not supported with a natively compiled Stored Procedure. |
| Disk-based tables |
|
| Views |
|
| DELETE / UPDATE with FROM clause |
A DELETE / UPDATE statement is not supported with a FROM clause with natively compiled Stored Procedures. |
| Isolation Level |
READ UNCOMMITTED isolation level is not supported with natively compiled Stored Procedures. |
| Sequences |
Sequences cannot be used inside natively compiled Stored Procedures. |
| HASH / MERGE |
HASH / MERGE joins are not supported. |
Keywords not available with Natively Compiled Store Procedures
| Keywords |
Remark or alternative approach |
| COMPUTE |
A COMPUTE clause is supported in natively compiled Stored Procedures. |
| OUTPUT |
It is not supported with a natively compiled Stored Procedure. We need to remove this clause. |
| EXECUTE WITH RECOMPILE |
WITH RECOMPILE cannot be used with a natively compiled Stored Procedure. |
| LEN and SUBSTRING |
A natively compiled Stored Procedure is not supported LEN and SUBSTRING function with _SC collation. |
| ALTER PROCEDURE |
A natively compiled Stored Procedure cannot be altered. Drop and recreate the Stored Procedure to change the definition of the procedure. |
OPENROWSET/
OPENQUERY/
OPENXML/
OPENDATASOURCE/
CONTAINSTABLE/
FREETEXTTABLE |
These keywords are not supported with a Natively compiled Stored Procedure. |
| GOTO |
This keyword is not supported. |
| EXECUTE, INSERT EXEC |
A nested procedure call cannot be used with a natively compiled Stored Procedure. |
| UNION |
|
| EXCEPT |
|
| OR, IN |
OR and IN keywords are not supported with the WHERE clause. |
| NOT / LIKE |
NOT and LIKE keywords are not supported with in natively compiled Stored Procedure. |
| ~, &, |, ^ (bitwise operators) and % (modulo) |
These operators cannot be used with natively compiled Stored Procedure. |
| ENCRYPTION |
ENCRYPTION is not supported |
| FOR REPLICATION/ FOR XML / FOR BROWSE |
These options are not supported. |
| GROUP BY without aggregate function
GROUP BY ALL
GROUP BY () |
When a query uses a GROUP BY clause, the query must have an aggregate function in the SELECT or HAVING clause.
ALL and GROUP BY with an empty list is not supported. |
| GROUPING SETS |
We cannot use GROUPING SETS with a GROUP BY clause. |
| ROLLUP / CUBE |
ROLLUP and CUBE cannot be used with a GROUP BY clause. |
Functions not available with Natively Compiled Store Procedures
| Functions |
Remark or alternative approach |
| CASE |
We cannot use a CASE Function with a query inside the natively compiled Stored Procedure. |
| User define function |
We cannot use a UDF within a natively compiled Stored Procedure. |
| User define aggregates |
We cannot use user-defined aggregates within a natively compiled Stored Procedure. |
| table-valued functions |
We cannot use a table value function with a natively compiled Stored Procedure. |
| MIN and MAX function with binary and character strings |
MIN and MAX functions cannot be used with a binary and character string. |
| Ranking function |
Ranking functions are not supported in a natively compiled Stored Procedure. |