Contents
What does native compilation mean for stored procedures?
Stored procedures that are marked with NATIVE_COMPILATION are natively compiled. This means the Transact-SQL statements in the procedure are all compiled to native code for efficient execution of performance-critical business logic. For more information about natively compiled stored procedures, see Natively Compiled Stored Procedures.
What happens to files generated by native compilation?
Database administrators do not need to maintain files that are generated by a native compilation. SQL Server automatically removes generated files that are no longer needed. For example, generated files will be deleted when a table and stored procedure is deleted, or if a database is dropped.
What does schemabinding do in native compiled Procs?
SCHEMABINDING – mandatory for native compilation to ensure that we cannot alter any of the objects referenced by the pre-compiled stored procedure without first dropping the stored procedure. EXECUTE AS – we must specify the execution context, as natively compiled stored procs do not support EXECUTE AS CALLER (the default context).
Can a temp table be used in a natively compiled Proc?
TempDB cannot be used. All access in a natively compiled stored procedure must be in-memory. You may use table variables or any other in-memory variables to achieve the same effect of a temp table. Incidentally, this TempDB restriction is the primary reason why so many other pieces of functionality are not allowed in natively-compiled stored procs.
Are there any SQL Server modules that are natively compiled?
Beginning with SQL Server 2016 (13.x), OR/NOT/IN/EXISTS operators are now supported in natively compiled modules. Aggregate functions AVG, COUNT, COUNT_BIG, MIN, MAX, and SUM. MIN and MAX are not supported for types nvarchar, char, varchar, varchar, varbinary, and binary.
Can you use schemabinding for natively compiled stored procedures?
SCHEMABINDING is only supported for natively compiled stored procedures in this version of SQL Server. BEGIN ATOMIC. The natively compiled stored procedure body must consist of exactly one atomic block. Atomic blocks guarantee atomic execution of the stored procedure.
How to update rows in a natively compiled stored procedure?
Statements inside natively compiled stored procedures update @@rowcount and you can use @@rowcount in a natively compiled stored procedure to determine the number of rows affected by the last statement executed within that natively compiled stored procedure.