如何在 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

关键要素解析

  1. WITH SCHEMABINDING, NATIVE_COMPILATION:
    • NATIVE_COMPILATION 指明该存储过程将被编译为原生机器指令。
    • SCHEMABINDING 是本机编译过程的强制要求(必须同时指定)。
  2. BEGIN ATOMIC WITH (...):
    • 本机编译过程必须包含一个 ATOMIC 事务代码块。
    • 块内必须显式声明事务隔离级别(如 SNAPSHOT、REPEATABLEREAD 或 SERIALIZABLE)以及语言设置。
  3. 架构保护生效:
    • 存储过程创建成功后,任何尝试对 dbo.Cows 表执行 ALTER TABLE 或 DROP TABLE 的操作都会被阻止,直到该存储过程被删除或修改。