Practical .NET

The Fastest Data Access Possible with Compiled Procs

• By Peter Vogel
• 10/09/2018

In a previous column, I talked about how a memory-optimized table can dramatically speed up the performance of the typical business application. But memory-optimized tables give you another tool to use in speeding up your applications: natively compiled stored procedures (that's a mouthful: I'll just call them compiled procs from here on in).

While you can access a memory-optimized table with plain old Transact-SQL, memory-optimized tables also support access through compiled procs. The key benefits to using compiled procs are faster processing and more efficient data access. However, compiled procedures also give you more design-time error checking and access to some T-SQL features only available in compiled procs (in a compiled proc, for example, you can specify that a parameter or variable will not accept nulls).

As with memory-optimized tables, compiled procs were introduced in SQL Server 2014 with some restrictions, many of which were removed in SQL Server 2016. For example, in SQL Server 2016 compiled process now support:

• The Top, and Distinct keywords in the Select clause
• Subqueries that return a single result in Select clauses
• Outer Joins
• And, Between, Or, Not, In, Exists in Where clauses

SQL Server 2017 permitted Case statements, the Apply operator (useful if you're going to use a table-valued expression in your compiled proc) and Execute For.

Creating a Natively Compiled Procedure
To create a compiled proc you add the Native_Compilation option to the With clause at the start of the procedure. However, there are some additional options that you must also include.

For example, the Schemabinding clause is required in compiled procs. You must also specify a transaction isolation level (something other than Read Uncommitted) and a language. Quite frankly, explicitly stating the transaction level, where possible, and applying schema binding are probably good ideas in any stored procedure.

You must also specify, in the procedure's Begin clause, that the procedure is Atomic. This option effectively means that the procedure is always in a transaction. The critical issue here is that if there is no transaction in play when the compiled proc starts, the compiled proc will create one and commit its changes at its end. If the next part of the process abends, the changes made by the compiled proc will still be in place even though the calling proc didn't create a transaction.

Many of the restrictions that remain on compiled procs fall out of the requirement to make the procedure Atomic: Begin, Commit, and Rollback are forbidden, for example and you can't use Set to change options within the procedure.

Putting that altogether, a compiled proc that inserts rows into the Transactions table I created in my previous column might look like this:

Create Procedure [dbo].[Add_Transaction] 
         (@orderid nchar(8) Not Null, @date DateTime Not Null, @quantityOnHand int = 0) 
With NATIVE_COMPILATION, SCHEMABINDING  
As Begin Atomic With  
(  
 Transaction Isolation Level = SNAPSHOT, Language = n'us_english'
)  
  Insert Into [dbo].[Transactions] (OrderId, Date, QuantityOnHand) 
	values (@orderid, @date, @quantityonhand);  
End

Unfortunately, if you have an existing stored procedure that, you think, would make more sense if it were natively compiled, you'll have to drop it and recreate the procedure: You can't alter an existing non-compiled procedure to make it a natively compiled one.

I won't say that the only thing users care about is faster response times. I will, however, say that speeding up a slow application is one of those things that shows up on your performance appraisal at the end of the year. Memory-optimized tables and natively compiled stored procedures might be the ticket to your next promotion.