Feature, Function and Keyword Not Supported by Natively Compiled Store Procedure

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.