Difference between natively compiled SP Template & Usual SP Template in azure SQL server

In this blog post, we will explore the difference between natively compiled stored procedure (SP) templates and usual SP templates in Azure SQL Server. Natively compiled SPs are a feature of In-Memory OLTP that allows you to optimize the performance of your transactions by compiling your SPs into native code and storing them in memory. Usual SPs are the traditional way of creating and executing SPs in SQL Server, which are interpreted at runtime and may incur more overhead.

The Main Difference

The main difference between natively compiled SPs and usual SPs is how they are executed. Natively compiled SPs are executed directly by the SQL Server Database Engine without any interpretation or compilation. This reduces CPU usage and improves the throughput of your transactions. Usual SPs are executed by the SQL Server Relational Engine, which parses, compiles, and optimizes the SP code before executing it. This may introduce some latency and consume more resources.

Second Difference

Another difference is how they are created and modified. Natively compiled SPs are created using a special syntax that specifies the schema and memory-optimized tables that are used by the SP. You cannot modify a natively compiled SP after it is created; you have to drop and recreate it with the new definition. Usual SPs are created using the standard CREATE PROCEDURE syntax and can be modified using the ALTER PROCEDURE syntax. You can also use dynamic SQL within a usual SP, which is not possible in a natively compiled SP.

Third Difference

A third difference is how they handle errors and transactions. Natively compiled SPs use a different error-handling mechanism than usual SPs. They do not support TRY-CATCH blocks or RAISERROR statements; instead, they use THROW statements to raise errors. They also do not support nested transactions or savepoints; they only support atomic transactions that either commit or roll back as a whole. Usual SPs support all these features and can use them to handle errors and transactions more flexibly.

Summary

In summary, natively compiled SPs and usual SPs are two different ways of creating and executing stored procedures in Azure SQL Server. Natively compiled SPs offer better performance and lower resource consumption, but they have some limitations and restrictions compared to usual SPs. You should choose the appropriate option for your scenario based on your requirements and trade-offs.