One of the major new features for Microsoft SQL Server 2014 is the ability to have In-Memory OLTP. If you have not heard of this, it boils down to tables are able to be completely stored in memory and all operations against that table and it’s data occur in memory. This gives SQL Server the ability of working with data without the I/O waits that are present in disk based tables. In versions of SQL Server prior to 2014, stored procedures were strictly an interpreted language. With the in-memory tables for SQL Server 2014 the ability to have Natively Compiled stored procedures was added. The advantage this presents is better performance when stored procedures are run against in memory tables. As with anything there are caveats that go along with it. Natively Compiled Stored Procedures will not run against disk-based tables and there are limitations as to what TSQL code you can use in them. Interpreted stored procedures will still run against an in-memory table, but has to do so through a query interop.
The part of the limitations that jumped out at me was the limitations to TSQL. There is definitely going to be a learning curve because there are some very common TSQL features that are not supported. The first thing that jumped out was that cursors are not supported! (Yay!) I know this will cause problems for some people, but I’ve always gone out of my way to find ways around using cursors. Temp tables will not be supported in the natively compiled stored procedures since they are created on disc. Natively compiled stored procedures cannot make interactions with tables that are stored on disc. This will require some work, but can be overcome by using table variables that have a defined table type. There is also an option of defining a SCHEMA_ONLY in-memory table to use as a temp table. This means that the table structure is durable, but not the data within it. When the SQL server is shut down any data within the SCHEMA_ONLY table(s) will be gone but the table structure will be re-created when the server starts.
Here are some of the features that I considered commonly used that will not be supported in a Natively Compiled Stored Procedure.
|Cursor||Set based logic or While Loops|
|Temp Tables||Table variables (not inline) or an in-memory table created as SCHEMA_ONLY|
|Inline Table Variables||Table variables can still be used, but the table type has to be created as a user defined table type.|
|Common Table Expressions (CTE)||Re-writing the query to not use a CTE.|
|Subqueries (nested queries)||Re-write the query to not use a Subquery.|
|User Defined Functions (UDF)||The logic from the UDF will have to be built into the stored procedure.|
|Execute or Insert Exec||Include the logic from the external stored proc|
|OR and IN and CASE||Create queries for each case|
|NOT||This is just a change in syntax. Use != instead|
For a full list of unsupported TSQL features in Natively Compiled Stored Procedures go here: http://msdn.microsoft.com/en-us/library/dn246937(v=sql.120).aspx
Also, here is the white paper on In-Memory OLTP if you want to dig deeper: