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.

Up Next
    Ebook Download
    View all
    Learn
    View all