Practical .NET

The Fastest Data Access Possible with Compiled Procs

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) 
As Begin Atomic With  
 Transaction Isolation Level = SNAPSHOT, Language = n'us_english'
  Insert Into [dbo].[Transactions] (OrderId, Date, QuantityOnHand) 
	values (@orderid, @date, @quantityonhand);  

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.

About the Author

Peter Vogel is a system architect and principal in PH&V Information Services. PH&V provides full-stack consulting from UX design through object modeling to database design. Peter tweets about his VSM columns with the hashtag #vogelarticles. His blog posts on user experience design can be found at

comments powered by Disqus


  • VS Code Now Has Apple Silicon Builds for Native Mac Development

    Goodbye Rosetta, hello M1. Visual Studio Code has been updated with new builds that let it run natively on machines with Apple Silicon (M1), the company's own ARM64 chips.

  • Visual Studio 2019 for Mac v8.9 Ships with .NET 6 Preview 1 Support

    During its Ignite 2021 online event for IT pros and developers this week, Microsoft shipped Visual Studio 2019 for Mac v8.9, arriving with out-of-the-box support for .NET 6 Preview 1, which the company also released recently.

  • Analyst: TypeScript Now Firmly in Top 10 Echelon (Ruby, Not So Much)

    RedMonk analyst Stephen O'Grady believes TypeScript has achieved the rare feat of firmly ensconcing itself into the top 10 echelon of his ranking, now questioning how high it might go.

  • Black White Wave IMage

    Neural Regression Using PyTorch: Training

    The goal of a regression problem is to predict a single numeric value, for example, predicting the annual revenue of a new restaurant based on variables such as menu prices, number of tables, location and so on.

Upcoming Events