Implementing Memory-Optimized Tables and Native Stored Procedures

Introduction

The Implementing Memory-Optimized Tables and Native Stored Procedures module provides you with the instructions and devices to develop your hands-on skills in the following topics.

• Working with memory-optimized tables
• Managing natively compiled stored procedures

Lab time: It will take approximately 60 minutes to complete this lab.

Exam Objectives

Three exam objectives are covered in this lab.

• Demonstrate how to create a memory-optimized table and load data into it
• Demonstrate how to create a natively compiled stored procedure and insert large data to the memory-optimized table created in the previous demo
• Demonstrate how to enable statistics collection on natively compiled stored procedures at the procedure level as well as query level
• Demonstrate how to collect the execution statistics for natively compiled stored procedures

Exercise 1 - Working with Memory-Optimized Tables

Memory-optimized tables in SQL Server are tables that reside in the main memory of the system. The data is read from and written to the memory. You can create memory-optimized tables using the CREATE TABLE statement. These tables are fully durable by default as the copy of the table data is maintained on the disk for recovery purposes in the event of a server failure. SQL Server also supports non-durable memory-optimized tables and the data of such tables is not maintained on the disk.

You can access data from the memory-optimized tables using natively compiled stored procedures or through interpreted Transact-SQL.

You can create three types of indexes on memory-optimized tables such as columnstore, nonclustered, and hash indexes.

In this exercise, you will work with memory-optimized tables.

Exercise 2 - Managing Natively Compiled Stored Procedures

Natively compiled stored procedures access data from memory-optimized tables. They are Transact-SQL stored procedures that facilitate efficient query execution. As the name suggests, the natively compiled stored procedures are compiled when they are created. You can create natively compiled stored procedures using the CREATE PROCEDURE statement.

The body of natively compiled stored procedure must include only one atomic block. An atomic block contains only one block of Transact-SQL statements.

In this exercise, you will perform the following tasks:

• Create natively compiled stored procedures and insert large data into memory-optimized table created in the previous exercise.
• Enable statistics collection on natively compiled stored procedures at the procedure and query level.
• Collect the execution statistics for natively compiled stored procedures.