In all versions of SQL Server to date, stored procedures written in T-SQL have been compiled into a form of intermediate language that would then be interpreted. In conjunction with SQL Server 2014, we now have the option to use stored procedures that are compiled all the way down to machine code.
Natively compiled queries only work against Memory Optimized Tables. If you also need to access traditional tables then you must use interpreted queries. This also means that you cannot use data types that are not supported by memory optimized tables.
In exchange for performance, natively compiled queries are incredibly limited in flexibility. As expected, you cannot use variables data types that are not supported by the tables themselves. Nor can you use obsolete techniques such as cursors.
More surprising is standard operations that are not supported. There is too much to summarize, so we present you the full list.
|
Type |
Feature |
Resolution |
|
Feature |
Table-valued parameters |
Table types cannot be used with parameters of natively compiled stored procedures. Use scalar data types instead. |
|
Feature |
Inline table variables |
Table types cannot be declared inline with variable declarations. Table types must be declared explicitly using a CREATE TYPE statement. |
|
Feature |
Cursors |
Cursors are not supported on or in natively compiled stored procedures. · When executing the procedure from the client, use RPC rather than the cursor API. With ODBC, avoid the Transact-SQL statement EXECUTE, instead specify the name of the procedure directly. · When executing the procedure from a Transact-SQL batch or another stored procedure, avoid using a cursor with the natively compiled stored procedure. · When creating a natively compiled stored procedure, rather than using a cursor, use set-based logic or a WHILE loop. |
|
Feature |
Non-constant parameter defaults |
When using default values with parameters on natively compiled stored procedures, the values must be constants. Remove any wildcards from the parameter declarations. |
|
Feature |
Natively compiled stored procedures cannot be numbered. Remove the ; number from the CREATE PROCEDURE statement. |
|
|
Feature |
multi-row table-valued constructor |
Cannot insert multiple rows using the same INSERT statement in a natively compiled stored procedure. Create INSERT statements for each row. |
|
Feature |
WITH clause |
Common table expressions (CTE) are not supported in natively compiled stored procedures. Rewrite the query. |
|
Feature |
recursive queries |
Recursive queries are not supported. Remove the query from the natively compiled stored procedure. |
|
Feature |
subquery |
Subqueries are not supported. Rewrite the query. |
|
Feature |
COMPUTE |
The COMPUTE clause is not supported. Remove it from the query. |
|
Feature |
SELECT INTO |
The INTO clause is not supported with the SELECT statement. Rewrite the query as INSERT INTO Table SELECT. |
|
Feature |
OUTPUT |
The OUTPUT clause is not supported. Remove it from the query. |
|
Feature |
incomplete insert column list |
In INSERT statements, values must be specified for all columns in the table. |
|
Function |
Function |
The built-in function is not supported in natively compiled stored procedures. Remove the function from the stored procedure. |
|
Feature |
CASE |
The CASE statement is not supported in queries inside natively compiled stored procedures. Create queries for each case. |
|
Feature |
user-defined functions |
User-defined functions cannot be used in natively compiled stored procedures. Remove the reference to the function from the procedure definition. |
|
Feature |
user-defined aggregates |
User-defined aggregate functions cannot be used in natively compiled stored procedures. Remove the reference to the function from the procedure. |
|
Feature |
browse mode metadata |
Natively compiled stored procedures do not support browse mode metadata. Make sure the session option NO_BROWSETABLE is set to OFF. |
|
Feature |
DELETE with FROM clause |
The FROM clause is not supported for DELETE statements in natively compiled stored procedures. |
|
Feature |
UPDATE with FROM clause |
The FROM clause is not supported for DELETE statements in natively compiled stored procedures. |
|
Feature |
temporary procedures |
Temporary stored procedures cannot be natively compiled. Either create a permanent natively compiled stored procedure or a temporary interpreted Transact-SQL stored procedure. |
|
Isolation level |
READ UNCOMMITTED |
The isolation level READ UNCOMMITTED is not supported for natively compiled stored procedures. Use a supported isolation level, such as SNAPSHOT. |
|
Isolation level |
READ COMMITTED |
The isolation level READ UNCOMMITTED is not supported for natively compiled stored procedures. Use a supported isolation level, such as SNAPSHOT. |
|
Feature |
temporary tables |
Tables in tempdb cannot be used in natively compiled stored procedures. Instead, use a table variable or a memory-optimized table with DURABILITY=SCHEMA_ONLY. |
|
Feature |
MARS |
Multiple Active Result Sets (MARS) is not supported with natively compiled stored procedures. This error can also indicate linked server use. Linked server can use MARS. Linked servers are not supported with natively compiled stored procedures. Instead, connect directly to the server and database that hosts the natively compiled stored procedures. |
|
Feature |
DTC |
Memory-optimized tables and natively compiled stored procedures cannot be accessed from distributed transactions. Use SQL transactions instead. |
|
Feature |
non-bin2 collation |
Comparison, sorting, and other operations on character strings in natively compiled stored procedures require using a BIN2 collation. Use the COLLATE clause or use columns and variables with an appropriate collation. For more information, see Collations and Code Pages. |
|
Feature |
Truncation of UTF-16 data. |
Character strings with an _SC collation use the UTF-16 encoding. Converting an n(var)char value to an n(var)char value with a shorted length involves truncation. This is not supported for UTF-16 values in natively compiled stored procedures. Avoid truncation of UTF-16 strings. |
|
Feature |
EXECUTE WITH RECOMPILE |
The option WITH RECOMPILE is not supported for natively compiled stored procedures. |
|
Feature |
Execution from the dedicated administrator connection. |
Natively compiled stored procedures cannot be executed from the dedicated admin connection (DAC). Use a regular connection instead. |
|
Operation |
ALTER PROCEDURE |
Natively compiled stored procedures cannot be altered. To change the procedure definition, drop and recreate the stored procedure. |
|
Operation |
savepoint |
Natively compiled stored procedures cannot be invoked from transactions that have an active savepoint. Remove the savepoint from the transaction. |
|
Operation |
ALTER AUTHORIZATION |
Changing the owner of an existing memory-optimized table or natively compiled stored procedure is not supported. Drop and recreate the table or procedure to change ownership. |
|
Operator |
OPENROWSET |
This operator is not supported. Remove OPENROWSET from the natively compiled stored procedure. |
|
Operator |
OPENQUERY |
This operator is not supported. Remove OPENQUERY from the natively compiled stored procedure. |
|
Operator |
OPENDATASOURCE |
This operator is not supported. Remove OPENDATASOURCE from the natively compiled stored procedure. |
|
Operator |
OPENXML |
This operator is not supported. Remove OPENXML from the natively compiled stored procedure. |
|
Operator |
CONTAINSTABLE |
This operator is not supported. Remove CONTAINSTABLE from the natively compiled stored procedure. |
|
Operator |
FREETEXTTABLE |
This operator is not supported. Remove FREETEXTTABLE from the natively compiled stored procedure. |
|
Feature |
table-valued functions |
Table-valued functions cannot be referenced from natively compiled stored procedures. Use reference tables instead. |
|
Operator |
CHANGETABLE |
This operator is not supported. Remove CHANGETABLE from the natively compiled stored procedure. |
|
Operator |
GOTO |
This operator is not supported. Use other procedural constructs such as WHILE. |
|
Operator |
EXECUTE, INSERT EXEC |
Nesting natively compiled stored procedures is not supported. The required operations can be specified inline, as part of the stored procedure definition. |
|
Operator |
OFFSET |
This operator is not supported. Remove OFFSET from the natively compiled stored procedure. |
|
Operator |
UNION |
This operator is not supported. Remove UNION from the natively compiled stored procedure. Combining several result sets into a single result set can be done using a table variable. |
|
Operator |
INTERSECT |
This operator is not supported. Remove INTERSECT from the natively compiled stored procedure. In some cases an INNER JOIN can be used to obtain the same result. |
|
Operator |
EXCEPT |
This operator is not supported. Remove EXCEPT from the natively compiled stored procedure. |
|
Operator |
OUTER JOIN |
This operator is not supported. Remove OUTER JOIN from the natively compiled stored procedure. |
|
Operator |
APPLY |
This operator is not supported. Remove APPLY from the natively compiled stored procedure. |
|
Operator |
PIVOT |
This operator is not supported. Remove PIVOT from the natively compiled stored procedure. |
|
Operator |
UNPIVOT |
This operator is not supported. Remove UNPIVOT from the natively compiled stored procedure. |
|
Operator |
OR, IN |
Disjunction (OR, IN) is not supported for queries in natively compiled stored procedures. Create queries for each of the cases. |
|
Operator |
CONTAINS |
This operator is not supported. Remove CONTAINS from the natively compiled stored procedure. |
|
Operator |
FREETEXT |
This operator is not supported. Remove FREETEXT from the natively compiled stored procedure. |
|
Operator |
NOT |
This operator is not supported. Remove NOT from the natively compiled stored procedure. In some cases, NOT can be replaced with inequality. For example, NOT a=b can be replaced with a!=b. |
|
Operator |
TSEQUAL |
This operator is not supported. Remove TSEQUAL from the natively compiled stored procedure. |
|
Operator |
LIKE |
This operator is not supported. Remove LIKE from the natively compiled stored procedure. |
|
Operator |
NEXT VALUE FOR |
Sequences cannot be referenced inside natively compiled stored procedures. Obtain the value using interpreted Transact-SQL, and then pass it into the natively compiled stored procedure. For more information, see Implementing IDENTITY in a Memory-Optimized Table. |
|
Operator |
~, &, |, ^ (bitwise operators) |
Bitwise operators are not supported. Remove them from the natively compiled stored procedure. |
|
Operator |
% (modulo) |
The module operator is not supported. Remove % from the natively compiled stored procedure. |
|
Set option |
option |
SET options cannot be changed inside natively compiled stored procedures. Certain options can be set with the BEGIN ATOMIC statement. For more information, see the section on atonic blocks in Natively Compiled Stored Procedures. |
|
Operand |
TABLESAMPLE |
This operator is not supported. Remove TABLESAMPLE from the natively compiled stored procedure. |
|
Option |
RECOMPILE |
Natively compiled stored procedures are compiled at create time. To recompile a natively compiled stored procedure, drop and recreate it. Remove RECOMPILE from the procedure definition. |
|
Option |
ENCRYPTION |
This option is not supported. Remove ENCRYPTION from the procedure definition. |
|
Option |
FOR REPLICATION |
Natively compiled stored procedures cannot be created for replication. Removed FOR REPLICATION from the procedure definition. |
|
Option |
FOR XML |
This option is not supported. Remove FOR XML from the natively compiled stored procedure. |
|
Option |
FOR BROWSE |
This option is not supported. Remove FOR BROWSE from the natively compiled stored procedure. |
|
Join hint |
HASH, MERGE |
Natively compiled stored procedures only support nested-loops joins. Hash and merge joins are not supported. Remove the join hint. |
|
Query hint |
Query hint |
This query hint is not inside natively compiled stored procedures. For supported query hints see Query Hints (Transact-SQL). |
|
Option |
DISTINCT |
This option is not supported. Remove DISTINCT from the query in the natively compiled stored procedure. |
|
Option |
PERCENT |
This option is not supported with TOP clauses. Remove PERCENT from the query in the natively compiled stored procedure. |
|
Option |
WITH TIES |
This option is not supported with TOP clauses. Remove WITH TIES from the query in the natively compiled stored procedure. |
|
Aggregate function |
Aggregate function |
This clause is not supported. For more information about aggregate functions in natively compiled stored procedures, see Natively Compiled Stored Procedures. |
|
Ranking function |
Ranking function |
Ranking functions are not supported in natively compiled stored procedures. Remove them from the procedure definition. |
|
Function |
Function |
This function is not supported. Remove it from the natively compiled stored procedure. |
|
Statement |
Statement |
This statement is not supported. Remove it from the natively compiled stored procedure. |
|
Feature |
MIN/MAX of deep type |
The aggregate functions MIN and MAX cannot be used for character and binary string values inside natively compiled stored procedures. |
|
Feature |
GROUP BY without aggregate function |
In natively compiled stored procedures, when a query has a GROUP BY clause, the query must also use an aggregate function. Add an aggregate function to the query. |
|
Feature |
ORDER BY expression not in the GROUP BY list |
For queries that contain both a GROUP BY and an ORDER BY clause, every item in the ORDER BY list must also appear identically in the GROUP BY list. |
|
Feature |
ROLLUP |
ROLLUP cannot be used with GROUP BY clauses in natively compiled stored procedures. Remove ROLLUP from the procedure definition. |
|
Feature |
CUBE |
CUBE cannot be used with GROUP BY clauses in natively compiled stored procedures. Remove CUBE from the procedure definition. |
|
Feature |
GROUPING SETS |
GROUPING SETS cannot be used with GROUP BY clauses in natively compiled stored procedures. Remove GROUPING SETS from the procedure definition. |
Please keep in mind that this list is subject to change over time. As we get closer to the final version, Microsoft is expecting to relax some of those restrictions.
Schema Binding
While most in-memory databases are focusing on their schema-less designs, Microsoft’s offering is going in the other direction. Natively compiled stored procedures are required to use the “SCHEMABINDING” option. This option locks them to their associated tables. Any table they reference cannot be dropped without first dropping all of the stored procedures that reference it.
Unfortunately normal stored procedures don’t support this option in SQL Server 2014.
Execution
Naively compiled stored procedures do not execute as the caller. Instead they are always created with the “EXECUTE AS” option set to “OWNER”, “SELF”, or a specific user.
Atomic Blocks
Atomic blocks are a little different than what we normally see, but since they are required here is a quote from the documentation.
BEGIN ATOMIC is part of the ANSI SQL standard. SQL Server supports atomic blocks only at the top-level of natively compiled stored procedures.
- Every natively compiled stored procedure contains exactly one block of Transact-SQL statements. This is an ATOMIC block.
- Non-native, interpreted Transact-SQL stored procedures and ad hoc batches do not support atomic blocks.
Atomic blocks are executed (atomically) within the transaction. Either all statements in the block succeed or the entire block will be rolled back to the savepoint that was created at the start of the block. In addition, the session settings are fixed for the atomic block. Executing the same atomic block in sessions with different settings will result in the same behavior, independent of the settings of the current session.
Internals
Originally this report was titled “Natively Compiled Queries”, but that doesn’t do justice to how deep this runs. When a memory optimized table is created, SQL Server will literally create a DLL specifically for that table. This DLL contains machine code written for the express purpose of modifying data in that table. Even the indexes are compiled into this DLL, so altering an index means rebuilding the table.
The documentation is somewhat unclear, but it appears that DLLs are also created for stored procedures. This may explain why natively compiled stored procedures cannot be altered. Instead you must drop the drop older procedure and then create its replacement.