如何在 SQL Server 中创建架构绑定存储过程
在 SQL Server 中,常规存储过程不支持 WITH SCHEMABINDING。只有针对内存优化表的本机编译存储过程(Natively Compiled Stored Procedure)才支持且必须进行架构绑定。本文详细介绍其配置先决条件与 T-SQL 实现语法。
在 SQL Server 中,我们可以通过 WITH SCHEMABINDING 选项为用户定义函数(UDF)或视图建立架构绑定,防止引用的底层表结构被修改或删除。
然而,对于存储过程,规则有所不同:
[!NOTE] 常规解释型存储过程不支持架构绑定。如果您尝试在普通的
CREATE PROCEDURE语句中加入WITH SCHEMABINDING,SQL Server 会返回错误:
Msg 10738, Level 15: The statement 'CREATE PROCEDURE' does not support the WITH SCHEMABINDING option.
在 SQL Server 中,**只有且必须是本机编译的存储过程(Natively Compiled Stored Procedure)**才能够启用架构绑定。本机编译存储过程是 SQL Server 内存 OLTP(In-Memory OLTP)架构的核心组件,它们直接被编译为机器码 DLL,因此必须绑定底层表架构以保证内存访问的结构确定性。
先决条件:配置内存优化数据库环境
在创建本机编译存储过程之前,数据库必须已配置内存优化文件组,且引用的表必须是内存优化表(Memory-Optimized Table)。
以下是准备环境所需的完整 T-SQL 脚本:
USE master;
GO
-- 1. 创建测试数据库
CREATE DATABASE Test;
GO
-- 2. 为数据库添加 MEMORY_OPTIMIZED_DATA 文件组
ALTER DATABASE Test
ADD FILEGROUP imoltp_test CONTAINS MEMORY_OPTIMIZED_DATA;
GO
-- 3. 向该文件组添加容器文件/目录
ALTER DATABASE Test
ADD FILE (NAME='imoltp_test1', FILENAME='/var/opt/mssql/data/imoltp_test1')
TO FILEGROUP imoltp_test;
GO
USE Test;
GO
-- 4. 创建内存优化表(表定义中必须包含非聚集主键与持久化选项)
CREATE TABLE dbo.Cows
(
CowId int IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
CowName varchar(70) NULL,
Phone varchar(10) NULL
)
WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
GO
[!TIP] 上述
FILENAME为 Linux / macOS 容器环境路径示例;若在 Windows 环境下,路径应写为D:\mssql\data\imoltp_test1。
创建架构绑定的本机编译存储过程
环境就绪后,即可通过 WITH SCHEMABINDING, NATIVE_COMPILATION 子句创建存储过程:
CREATE PROCEDURE dbo.usp_GetCowsByName
@cowname varchar(70)
WITH SCHEMABINDING, NATIVE_COMPILATION
AS
BEGIN ATOMIC WITH (
TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'us_english'
)
SELECT
CowId,
CowName,
Phone
FROM dbo.Cows
WHERE CowName = @cowname
END;
GO
关键要素解析
WITH SCHEMABINDING, NATIVE_COMPILATION:NATIVE_COMPILATION指明该存储过程将被编译为原生机器指令。SCHEMABINDING是本机编译过程的强制要求(必须同时指定)。
BEGIN ATOMIC WITH (...):- 本机编译过程必须包含一个 ATOMIC 事务代码块。
- 块内必须显式声明事务隔离级别(如
SNAPSHOT、REPEATABLEREAD或SERIALIZABLE)以及语言设置。
- 架构保护生效:
- 存储过程创建成功后,任何尝试对
dbo.Cows表执行ALTER TABLE或DROP TABLE的操作都会被阻止,直到该存储过程被删除或修改。
- 存储过程创建成功后,任何尝试对