创建内存优化表和本机编译的存储过程
适用于：
SQL Server
本主题包含一个示例，该示例介绍内存中 OLTP 的语法。
若要使应用程序可使用内存中 OLTP，需要完成以下任务：
创建内存优化的数据文件组并且将容器添加到该文件组。
创建内存优化的表和索引。 有关详细信息，请参阅 CREATE TABLE (Transact-SQL)。
在加载数据后且在创建编译的存储过程之前，将数据加载到内存优化表中并且更新统计信息。 有关详细信息，请参阅 内存优化表的统计信息。
创建本机编译的存储过程以访问内存优化表中的数据。 有关详细信息，请参阅 CREATE PROCEDURE (Transact-SQL)。 还可以使用传统的、解释的 Transact-SQL 来访问内存优化表中的数据。
根据需要，将数据从现有表迁移到内存优化的表。
内存中对象的背景
有关如何使用 SQL Server Management Studio 创建内存优化表的信息，请参阅SQL Server Management Studio对 In-Memory OLTP 的支持。
本机编译的存储过程
本机编译的存储过程是编译为本机代码并访问内存优化表的 Transact-SQL 存储过程。 通过本机编译的存储过程，可在存储过程中高效执行查询和业务逻辑。 有关本机编译过程的更详细信息，请参阅 Native Compilation of Tables and Stored Procedures。 有关将基于磁盘的存储过程迁移到本机编译的存储过程的详细信息，请参阅 本机编译的存储过程的迁移问题。
注意
解释型（基于磁盘的）存储过程与本机编译的存储过程之间的一个区别在于解释型存储过程是在首次执行时编译的，而本机编译的存储过程是在创建时编译的。 对于本机编译的存储过程，可在创建时检测到许多错误情况（例如算术溢出、类型转换和一些被零除的情况），并且将导致本机编译的存储过程的创建失败。 对于解释型存储过程，在创建存储过程时这些错误情况通常不会导致失败，但所有执行都将失败。
T-SQL 中的代码示例
下面的代码示例需要名为 c:\Data\ 的目录。
CREATE DATABASE imoltp
GO
--------------------------------------
-- create database with a memory-optimized
-- filegroup and a container.
ALTER DATABASE imoltp ADD FILEGROUP imoltp_mod
CONTAINS MEMORY_OPTIMIZED_DATA;
ALTER DATABASE imoltp ADD FILE (
name='imoltp_mod1', filename='c:\data\imoltp_mod1')
TO FILEGROUP imoltp_mod;
ALTER DATABASE imoltp
SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
GO
USE imoltp
GO
-- Create a durable (data will be persisted) memory-optimized table
-- two of the columns are indexed.
CREATE TABLE dbo.ShoppingCart (
ShoppingCartId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
UserId INT NOT NULL INDEX ix_UserId NONCLUSTERED
HASH WITH (BUCKET_COUNT=1000000),
CreatedDate DATETIME2 NOT NULL,
TotalPrice MONEY
) WITH (MEMORY_OPTIMIZED=ON)
GO
-- Create a non-durable table. Data will not be persisted,
-- data loss if the server turns off unexpectedly.
CREATE TABLE dbo.UserSession (
SessionId INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED
HASH WITH (BUCKET_COUNT=400000),
UserId int NOT NULL,
CreatedDate DATETIME2 NOT NULL,
ShoppingCartId INT,
INDEX ix_UserId NONCLUSTERED
HASH (UserId) WITH (BUCKET_COUNT=400000)
)
WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY)
GO
-- insert data into the tables
INSERT dbo.UserSession VALUES (342, SYSDATETIME(), 4);
INSERT dbo.UserSession VALUES (65, SYSDATETIME(), NULL)
INSERT dbo.UserSession VALUES (8798, SYSDATETIME(), 1)
INSERT dbo.UserSession VALUES (80, SYSDATETIME(), NULL)
INSERT dbo.UserSession VALUES (4321, SYSDATETIME(), NULL)
INSERT dbo.UserSession VALUES (8578, SYSDATETIME(), NULL)
INSERT dbo.ShoppingCart VALUES (8798, SYSDATETIME(), NULL)
INSERT dbo.ShoppingCart VALUES (23, SYSDATETIME(), 45.4)
INSERT dbo.ShoppingCart VALUES (80, SYSDATETIME(), NULL)
INSERT dbo.ShoppingCart VALUES (342, SYSDATETIME(), 65.4)
GO
-- Verify table contents.
SELECT * FROM dbo.UserSession;
SELECT * FROM dbo.ShoppingCart;
GO
-- Update statistics on memory-optimized tables;
UPDATE STATISTICS dbo.UserSession WITH FULLSCAN, NORECOMPUTE;
UPDATE STATISTICS dbo.ShoppingCart WITH FULLSCAN, NORECOMPUTE;
GO
-- in an explicit transaction, assign a cart to a session
-- and update the total price.
-- SELECT/UPDATE/DELETE statements in explicit transactions.
BEGIN TRAN;
UPDATE dbo.UserSession SET ShoppingCartId=3 WHERE SessionId=4;
UPDATE dbo.ShoppingCart SET TotalPrice=65.84 WHERE ShoppingCartId=3;
COMMIT;
GO
-- Verify table contents.
SELECT *
FROM dbo.UserSession u
JOIN dbo.ShoppingCart s on u.ShoppingCartId=s.ShoppingCartId
WHERE u.SessionId=4;
GO
-- Natively compiled stored procedure for assigning
-- a shopping cart to a session.
CREATE PROCEDURE dbo.usp_AssignCart @SessionId int
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'us_english');
DECLARE @UserId INT,
@ShoppingCartId INT;
SELECT @UserId=UserId, @ShoppingCartId=ShoppingCartId
FROM dbo.UserSession WHERE SessionId=@SessionId;
IF @UserId IS NULL
THROW 51000, N'The session or shopping cart does not exist.', 1;
UPDATE dbo.UserSession
SET ShoppingCartId=@ShoppingCartId WHERE SessionId=@SessionId;
END
GO
EXEC usp_AssignCart 1;
GO
-- natively compiled stored procedure for inserting
-- a large number of rows this demonstrates the
-- performance of native procs
CREATE PROCEDURE dbo.usp_InsertSampleCarts @InsertCount int
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'us_english');
DECLARE @i int = 0;
WHILE @i < @InsertCount
BEGIN
INSERT INTO dbo.ShoppingCart VALUES (1, SYSDATETIME() , NULL)
SET @i += 1
END
END
GO
-- insert 1,000,000 rows
EXEC usp_InsertSampleCarts 1000000
GO
---- verify the rows have been inserted
SELECT COUNT(*) FROM dbo.ShoppingCart
GO
-- Sample memory-optimized tables for
-- sales orders and sales order details.
CREATE TABLE dbo.SalesOrders
(
so_id INT NOT NULL PRIMARY KEY NONCLUSTERED,
cust_id INT NOT NULL,
so_date DATE NOT NULL INDEX ix_date NONCLUSTERED,
so_total MONEY NOT NULL,
INDEX ix_date_total NONCLUSTERED
(so_date DESC, so_total DESC)
) WITH (MEMORY_OPTIMIZED=ON);
GO
CREATE TABLE dbo.SalesOrderDetails
(
so_id INT NOT NULL,
lineitem_id INT NOT NULL,
product_id INT NOT NULL,
unitprice MONEY NOT NULL,
CONSTRAINT PK_SOD PRIMARY KEY NONCLUSTERED
(so_id,lineitem_id)
) WITH (MEMORY_OPTIMIZED=ON)
GO
-- Memory-optimized table type for collecting
-- sales order details.
CREATE TYPE dbo.SalesOrderDetailsType AS TABLE
(
so_id INT NOT NULL,
lineitem_id INT NOT NULL,
product_id INT NOT NULL,
unitprice MONEY NOT NULL,
PRIMARY KEY NONCLUSTERED (so_id,lineitem_id)
) WITH (MEMORY_OPTIMIZED=ON)
GO
-- stored procedure that inserts a sales order,
-- along with its details.
CREATE PROCEDURE dbo.InsertSalesOrder
@so_id INT, @cust_id INT,
@items dbo.SalesOrderDetailsType READONLY
WITH NATIVE_COMPILATION, SCHEMABINDING
AS BEGIN ATOMIC WITH
(
TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'us_english'
)
DECLARE @total MONEY
SELECT @total = SUM(unitprice) FROM @items
INSERT dbo.SalesOrders
VALUES (@so_id, @cust_id, getdate(), @total)
INSERT dbo.SalesOrderDetails
SELECT so_id, lineitem_id, product_id, unitprice
FROM @items
END
GO
-- Insert a sample sales order.
DECLARE @so_id INT = 18,
@cust_id INT = 8,
@items dbo.SalesOrderDetailsType;
INSERT @items VALUES
(@so_id, 1, 4, 43),
(@so_id, 2, 3, 3),
(@so_id, 3, 8, 453),
(@so_id, 4, 5, 76),
(@so_id, 5, 4, 43);
EXEC dbo.InsertSalesOrder @so_id, @cust_id, @items;
GO
-- verify the content of the tables
SELECT
so.so_id,
so.so_date,
sod.lineitem_id,
sod.product_id,
sod.unitprice
FROM dbo.SalesOrders so
JOIN dbo.SalesOrderDetails sod on so.so_id=sod.so_id
ORDER BY so.so_id, sod.lineitem_id
另请参阅
反馈
即将发布：在整个 2024 年，我们将逐步淘汰作为内容反馈机制的“GitHub 问题”，并将其取代为新的反馈系统。 有关详细信息，请参阅：https://aka.ms/ContentUserFeedback。
提交和查看相关反馈