Memory-optimized tables can be accessed in two different ways: either through interpreted T-SQL or through natively compiled stored procedures. The following two subsections describe both access types.
1. Interpreted Transact-SQL
When you use interpreted T-SQL, you have access to all statements when working with your memory-optimized tables, but the performance of their execution will be worse than in the case of using compiled stored procedures. Interpreted T-SQL (also called “interop”) should be used primarily when you need to access both memory-optimized and disk-based tables.
Two T-SQL statements that are not supported when accessing memory-optimized tables using interpreted SQL are TRUNCATE TABLE and MERGE (when a memory-optimized table is the target). There are also a few other limitations, which can be found in Microsoft Docs.
2. Compiled Stored Procedures
Generally, the Database Engine uses an interpreter to execute stored procedures. (An interpreter is a computer program that directly executes single instructions written in a programming or scripting language, without previously compiling them into a machine code.) On the other hand, compilation (or native compilation) generally means that programming instructions are converted to machine code. After that, such code can be executed directly by the CPU, without the need for further compilation or interpretation.
Particularly, the In-Memory OLTP compiler takes high-level constructs, such as SELECT or INSERT, and compiles them into machine code. The inputs of the In-Memory OLTP compiler are memory-optimized objects, which are first converted into C code, and after that the C compiler generates the machine code. The compilation of memory-optimized objects results in dynamic-link libraries (DLLs) that are loaded in memory and linked into the Database Engine process.
Example 21.6 shows the creation of the project_mem memory-optimized table. (It has the same structure as the project table from the sample database.)
Example 21.6
USE sample2;
CREATE TABLE project_mem (project_no INT NOT NULL PRIMARY KEY
NONCLUSTERED HASH WITH (BUCKET_COUNT = 1024),
project_name CHAR (20), budget DEC(8,2))
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
The project_mem memory-optimized table is referenced by the compiled stored procedure created in Example 21.7.
Example 21.7
USE sample2;
GO
CREATE PROCEDURE dbo.increase_budget_mem (@percent INT=10)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(TRANSACTION ISOLATION LEVEL = SNAPSHOT,LANGUAGE = N’us_english’)
UPDATE dbo.project_mem
SET budget = budget + budget * @percent/100
END
The increase_budget_mem stored procedure has the same logic as the increase_budget procedure, which is used in Chapter 8 (see Example 8.6). The main difference is that the former will be natively compiled, while the latter is interpreted.
The NATIVE_COMPILATION option in the WITH clause specifies that the stored procedure will be compiled. Together with this option you have to specify two other options: SCHEMABINDING and EXECUTE AS. SCHEMABINDING declares that all tables referenced by the compiled stored procedure cannot be dropped without first dropping the stored procedure. The EXECUTE AS option is used to specify the security context under which to execute the stored procedure after it is accessed. By specifying the context in which the procedure is executed, you can control which user account the Database Engine uses to validate permissions on objects referenced by the procedure. In Example 21.7 we use the value OWNER to specify the owner context. (For more information about the EXECUTE AS option, see Chapter 8.)
Additionally, a natively compiled stored procedure needs to consist of one atomic block of code, which means that all statements in the code block are contained within a single transaction. In the ATOMIC WITH clause you need to specify the transaction isolation level and language.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.