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


  • AI for GitHub Collaboration? Maybe Not So Much

    No doubt GitHub Copilot has been a boon for developers, but AI might not be the best tool for collaboration, according to developers weighing in on a recent social media post from the GitHub team.

  • Visual Studio 2022 Getting VS Code 'Command Palette' Equivalent

    As any Visual Studio Code user knows, the editor's command palette is a powerful tool for getting things done quickly, without having to navigate through menus and dialogs. Now, we learn how an equivalent is coming for Microsoft's flagship Visual Studio IDE, invoked by the same familiar Ctrl+Shift+P keyboard shortcut.

  • .NET 9 Preview 3: 'I've Been Waiting 9 Years for This API!'

    Microsoft's third preview of .NET 9 sees a lot of minor tweaks and fixes with no earth-shaking new functionality, but little things can be important to individual developers.

  • Data Anomaly Detection Using a Neural Autoencoder with C#

    Dr. James McCaffrey of Microsoft Research tackles the process of examining a set of source data to find data items that are different in some way from the majority of the source items.

  • What's New for Python, Java in Visual Studio Code

    Microsoft announced March 2024 updates to its Python and Java extensions for Visual Studio Code, the open source-based, cross-platform code editor that has repeatedly been named the No. 1 tool in major development surveys.

Subscribe on YouTube