SQL Server 2017 Administrator's Guide

Natively compiled stored procedures

Natively compiled stored procedures are written in T-SQL. When such a procedure is created, it's saved into metadata and compiled into C. It makes natively compiled stored procedures very efficient, but the C compilation also causes a lot of limitations. Let's take a look at the following example:

create proc procNativeModification  (@Id int  , @UserId int  , @MoveiId int  , @Rating int)with native_compilation, schemabindingasbegin atomic with (transaction isolation level = snapshot, language = N'English')  if @id is null    insert dbo.Ratings_InMem (UserId, MovieId, Rating)     values (@UserId, @MoveiId, @Rating)  else    update dbo.Ratings_InMem     set UserId = @UserId, MovieId = @MoveiId, Rating = @Rating  where Id = ...