SQL Server 应用

存储过程

在 SQL Server 中，存储过程（Stored Procedure）是一组为了完成特定功能而预先编写好、并存储在数据库中的 SQL 语句集合

你可以把它想象成编程语言里的“函数”或“方法”,把复杂的业务逻辑封装在一个存储过程里，以后只需要调用它的名字就能执行，而不需要每次都重新写一遍长长的 SQL 代码

基本语法

✨ 创建存储过程

CREATE PROCEDURE 存储过程名称
    @参数名1 数据类型,
    @参数名2 数据类型 OUTPUT -- OUTPUT 表示输出参数
AS
BEGIN
    -- 这里写具体的 SQL 逻辑
    SELECT * FROM Employees WHERE Department = @参数名1;
END

执行（调用）存储过程

EXEC 存储过程名称 @参数名1 = '销售部';

存储过程核心优势

提升性能：存储过程在第一次创建执行时会被编译，后续再调用时直接执行编译好的执行计划，省去了反复解析和编译 SQL 语句的时间
减少网络流量:客户端只需要发送一句 EXEC 存储过程名的指令，而不需要通过网络发送几百行的 SQL 代码，大大减轻了网络负担
增强安全性：你可以只给用户赋予执行某个存储过程的权限，而不给他们直接查询或修改底层表的权限，从而有效防止 SQL 注入攻击
代码复用与维护：复杂的业务逻辑只需写一次，所有应用程序都可以调用,如果业务逻辑变了，只需要修改数据库里的存储过程，不需要去改动前端或后端的程序代码

🚩 常用场景与代码示例

无参数的存储过程

-- 创建一个查询所有员工信息的存储过程
CREATE PROCEDURE GetEmployeeList
AS
BEGIN
    SELECT Name, Department, Salary FROM Employees;
END
 
-- 执行
EXEC GetEmployeeList;

带输入参数的存储过程

-- 根据部门名称查询员工
CREATE PROCEDURE GetEmployeesByDept
    @DeptName NVARCHAR(50) -- 输入参数
AS
BEGIN
    SELECT Name, Salary FROM Employees WHERE Department = @DeptName;
END
 
-- 执行并传入参数
EXEC GetEmployeesByDept @DeptName = '销售部';

带输入和输出参数的存储过程

-- 查询指定部门的员工人数，并将人数作为结果输出
CREATE PROCEDURE GetDeptCount
    @DeptName NVARCHAR(50),      -- 输入参数
    @EmpCount INT OUTPUT         -- 输出参数
AS
BEGIN
    SELECT @EmpCount = COUNT(*) 
    FROM Employees 
    WHERE Department = @DeptName;
END
 
-- 执行并接收输出结果
DECLARE @TotalCount INT;
EXEC GetDeptCount @DeptName = '销售部', @EmpCount = @TotalCount OUTPUT;
SELECT @TotalCount AS 部门总人数;

👀 查看存储过程

sys.sql_modules

SELECT definition 
FROM sys.sql_modules 
WHERE object_id = OBJECT_ID('存储过程名称');

使用 sp_helptext（经典系统存储过程）
1. EXEC sp_helptext '存储过程名称';
使用 OBJECT_DEFINITION 函数
1. SELECT OBJECT_DEFINITION(OBJECT_ID('存储过程名称'));

✏️ 修改存储过程

ALTER PROCEDURE 存储过程名称
    @参数名 数据类型
AS
BEGIN
    -- 这里写修改后的新 SQL 逻辑
    SELECT * FROM 表名 WHERE 条件 = @参数名;
END

🏷️ 重命名存储过程

EXEC sp_rename '旧存储过程名称', '新存储过程名称';

🗑️ 删除存储过程

DROP PROCEDURE 存储过程名称;
DROP PROCEDURE IF EXISTS 存储过程名称; ¹⁾

触发器

在 SQL Server 中，触发器（Trigger）是一种特殊的存储过程,它和普通的存储过程最大的区别在于：普通存储过程需要你用 EXEC 显式调用，而触发器是自动执行的 ²⁾

DML 触发器（数据操作语言触发器）:这是最常用的触发器，专门用于响应表或视图的数据更改（INSERT、UPDATE、DELETE）
DDL 触发器（数据定义语言触发器）:用于响应数据库结构的更改，比如 CREATE、ALTER、DROP 语句
登录触发器（Logon Trigger）: 在用户建立数据库连接会话（LOGON 事件）时触发。常用于限制特定用户在特定时间登录，或者记录登录日志

🚩 常用场景与代码示例

场景：记录数据变更日志（AFTER UPDATE 触发器）:假设我们想在员工薪资发生变动时，自动把变动记录保存到一张日志表里

-- 创建一个 AFTER UPDATE 触发器
CREATE TRIGGER tr_LogSalaryChange
ON Employees
AFTER UPDATE
AS
BEGIN
    -- 只有当 Salary 列被更新时才执行
    IF UPDATE(Salary)
    BEGIN
        INSERT INTO SalaryChangeLog (EmployeeID, OldSalary, NewSalary, ChangeDate)
        SELECT 
            d.EmployeeID, 
            d.Salary AS OldSalary, 
            i.Salary AS NewSalary, 
            GETDATE()
        FROM deleted d
        INNER JOIN inserted i ON d.EmployeeID = i.EmployeeID
        WHERE d.Salary <> i.Salary; -- 确保薪资真的发生了变化
    END
END

场景：防止核心表被误删（DDL 触发器）

-- 创建一个数据库级别的 DDL 触发器，拦截所有的 DROP TABLE 操作
CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE
AS
BEGIN
    PRINT '禁止删除表！请先联系数据库管理员。';
    ROLLBACK; -- 回滚操作，取消删除
END

✨ 创建触发器

创建触发器使用 CREATE TRIGGER 语句,你需要指定它绑定的表、触发时机（AFTER 或 INSTEAD OF）以及触发的具体事件（INSERT、UPDATE、DELETE）

CREATE TRIGGER tr_Employee_Audit
ON Employees
AFTER INSERT, UPDATE
AS
BEGIN
    -- 这里写触发后执行的逻辑，比如插入日志表
    PRINT '员工表数据发生了变动！';
END

👀 查看触发器

EXEC sp_helptext 'tr_Employee_Audit';

查看某张表上所有的触发器信息: EXEC sp_helptrigger 'Employees';

通过系统视图查看更详细的状态（如是否被禁用）

SELECT name, is_disabled FROM sys.triggers WHERE parent_id = OBJECT_ID('Employees');

✏️ 修改触发器 (ALTER)

修改触发器的逻辑时，强烈建议使用 ALTER TRIGGER,它和创建时的语法几乎一模一样，只是把 CREATE 换成了 ALTER

ALTER TRIGGER tr_Employee_Audit
ON Employees
AFTER INSERT -- 修改为只在插入时触发
AS
BEGIN
    PRINT '员工表发生了新增操作！';
END

🏷️ 重命名触发器 (RENAME)

SQL Server 官方非常不建议使用 sp_rename 来重命名触发器，因为这只会改“外壳”，不会修改系统视图（sys.sql_modules）里的底层定义，容易引发后续维护的混乱

最规范、最安全的做法是：先删除，再用新名字重新创建

-- 1. 删除旧触发器
DROP TRIGGER tr_Employee_Audit;
GO
-- 2. 用新名字重新创建
CREATE TRIGGER tr_Employee_NewAudit
ON Employees
AFTER INSERT
AS ...

禁用触发器 (DISABLE)

DISABLE TRIGGER tr_Employee_Audit ON Employees;

启用触发器 (ENABLE)

ENABLE TRIGGER tr_Employee_Audit ON Employees;

🗑️ 删除触发器 (DROP)

-- 标准删除
DROP TRIGGER tr_Employee_Audit;
 
-- 安全删除（SQL Server 2016 及以上版本推荐，防止触发器不存在时报错）
DROP TRIGGER IF EXISTS tr_Employee_Audit;

游标

在 SQL Server 中，游标（Cursor）是一种数据库对象，它允许你逐行地处理查询结果集中的数据,你可以把它想象成编程里的“指针”或者一个“数据游标”

普通的 SQL 查询（比如 SELECT）是面向集合的，一次性返回所有符合条件的行；而游标则是面向行的，它让你能够像遍历数组一样，一行一行地读取、修改或执行复杂的业务逻辑

📝 游标的基本使用步骤

使用游标通常遵循固定的“五步走”流程

声明游标(DECLARE)

DECLARE MyCursor CURSOR FOR 
SELECT Name, Salary FROM Employees WHERE Department = '销售部';

打开游标（OPEN）: OPEN MyCursor;

提取数据（FETCH）

-- 声明变量来接收数据
DECLARE @EmpName NVARCHAR(50), @EmpSalary DECIMAL(10,2);
 
-- 读取第一行
FETCH NEXT FROM MyCursor INTO @EmpName, @EmpSalary;

循环处理（WHILE 循环）:配合 @@FETCH_STATUS 全局变量（0 表示成功读取，-1 表示失败，-2 表示行丢失）来遍历所有行

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 在这里写对每一行数据的具体处理逻辑
    PRINT '员工姓名：' + @EmpName + '，薪资：' + CAST(@EmpSalary AS VARCHAR);
 
    -- 继续读取下一行
    FETCH NEXT FROM MyCursor INTO @EmpName, @EmpSalary;
END

关闭并释放游标（CLOSE & DEALLOCATE）
```
CLOSE MyCursor;
DEALLOCATE MyCursor;
```

使用系统过程查看游标

在 SQL Server 中，最常用的查看游标的系统过程是 sp_cursor_list

-- 声明一个变量来接收游标列表
DECLARE @Report CURSOR;
 
-- 调用系统过程，把当前所有的游标信息赋值给这个变量
EXEC sp_cursor_list @cursor_return = @Report OUTPUT, @cursor_scope = 2;
 
-- 查看这个“监控大屏”上的信息
FETCH NEXT FROM @Report;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    FETCH NEXT FROM @Report;
END
 
-- 看完记得关闭监控
CLOSE @Report;
DEALLOCATE @Report;

服务器游标的属性

在 SQL Server 中，要报告服务器游标的属性，最标准的方法是使用系统存储过程 sp_describe_cursor

🚩 假设我们有一个员工表，我们先创建一个游标，然后调用系统过程来查看它的属性

-- 1. 声明并打开一个全局游标
DECLARE MyCursor CURSOR GLOBAL FOR 
SELECT Name, Salary FROM Employees WHERE Department = '销售部';
 
OPEN MyCursor;
GO
 
-- 2. 声明一个游标变量，用来接收“体检报告”
DECLARE @CursorReport CURSOR;
 
-- 3. 调用系统过程 sp_describe_cursor 生成报告
-- 参数说明：
-- @cursor_return: 接收报告的变量
-- @cursor_source: 指定游标来源（'global' 表示全局游标）
-- @cursor_identity: 指定游标的名字
EXEC master.dbo.sp_describe_cursor 
    @cursor_return = @CursorReport OUTPUT,
    @cursor_source = N'global',
    @cursor_identity = N'MyCursor';
 
-- 4. 读取并展示这份“体检报告”
FETCH NEXT FROM @CursorReport;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    FETCH NEXT FROM @CursorReport;
END
 
-- 5. 清理工作：关闭并释放报告游标
CLOSE @CursorReport;
DEALLOCATE @CursorReport;
GO
 
-- 6. 清理工作：关闭并释放我们最初创建的业务游标
CLOSE MyCursor;
DEALLOCATE MyCursor;

索引

在 SQL Server 中，索引（Index）是一种用于快速查找和访问数据库中数据的特殊数据结构,索引的核心分类:行存储索引和列存储索引

非聚集索引、聚集索引

💻 创建索引的 T-SQL 语法

创建非聚集索引

-- 在员工表的 Department 列上创建一个非聚集索引
CREATE NONCLUSTERED INDEX IX_Employees_Department
ON Employees (Department);

创建聚集索引

-- 通常在创建表时指定主键，系统会自动创建聚集索引
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY, -- 默认创建聚集索引
    Name NVARCHAR(50)
);

删除索引: DROP INDEX IX_Employees_Department ON Employees;

设置索引的选项

性能与并发控制选项
1. ONLINE = { ON | OFF }
  1. 作用: 决定是否在创建或重建索引时允许用户继续查询和修改表数据
  2. 场景：生产环境强烈建议设置为 ON。虽然会稍微慢一点，但不会锁表，业务不受影响
2. MAXDOP = n
  1. 作用：限制创建索引时使用的 CPU 核心数（最大并行度）
  2. 场景：在服务器资源紧张时，可以设置为 1 或较小的值，防止索引操作占满所有 CPU 导致业务卡顿
3. SORT_IN_TEMPDB = { ON | OFF }
  1. 作用：决定创建索引时的中间排序结果是否存放在 tempdb 系统库中
  2. 场景：如果你的 tempdb 在高速 SSD 上，设置为 ON 可以显著加快索引创建速度，但会消耗更多磁盘空间
存储与空间优化选项
1. DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
  1. 作用：设置索引的数据压缩级别
  2. 场景：对于数据量巨大的表，开启 PAGE 压缩可以大幅节省磁盘空间，并减少内存和 I/O 开销，但会消耗少量 CPU 资源
2. PAD_INDEX = { ON | OFF }
  1. 作用：决定是否对索引的中间层级页面也应用填充因子（Fill Factor）的空白空间
  2. 场景：通常与 FILLFACTOR 配合使用，用于减少索引页拆分
3. FILLFACTOR = n
  1. 作用：指定创建索引时，每个索引页填充的百分比（1-100）
  2. 场景：对于频繁插入更新的表，设置为 80 或 90 可以预留空间，减少未来的“页拆分”开销
维护与管理选项
1. DROP_EXISTING = { ON | OFF }
  1. 作用：在重建现有索引时，先删除旧索引再创建新索引
  2. 场景：当你需要修改聚集索引的键列，或者改变索引的文件组时，必须设置为 ON。它能避免非聚集索引被重建两次，效率更高
2. STATISTICS_NORECOMPUTE = { ON | OFF }
  1. 作用：决定是否自动更新索引的统计信息
  2. 场景：通常保持默认的 OFF（自动更新）,只有在极少数需要手动精确控制统计信息更新策略时，才会设置为 ON
3. IGNORE_DUP_KEY = { ON | OFF }
  1. 作用：当向唯一索引插入重复键值时，是只报错当前行（ON）还是回滚整个事务（OFF）
  2. 场景：默认是 OFF,只有在需要批量插入数据且允许忽略个别重复项时，才会考虑设置为 ON

🚩 语法示例

CREATE NONCLUSTERED INDEX IX_Employees_Department
ON Employees (Department)
WITH (
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = ON,       -- 利用 tempdb 加速排序
    DROP_EXISTING = OFF,       -- 新建索引，不是重建
    ONLINE = ON,               -- 允许在线操作，不锁表
    ALLOW_ROW_LOCKS = ON,      -- 允许行锁
    ALLOW_PAGE_LOCKS = ON,     -- 允许页锁
    DATA_COMPRESSION = PAGE    -- 开启页压缩，节省空间
)
ON [PRIMARY];                  -- 指定存储的文件组

索引的分析

📊 SET STATISTICS IO ON（看真实的 I/O 消耗）

这个开关的作用是：在查询执行后，返回该语句对磁盘和内存的真实读取次数。它是判断 SQL 语句性能最客观的指标之一

SET STATISTICS IO ON;
GO
-- 你的查询语句
SELECT * FROM Employees WHERE Department = '销售部';
GO
SET STATISTICS IO OFF;

输出结果解读：

执行后，在“消息”栏会看到类似这样的信息：

表 'Employees'。扫描计数 1，逻辑读取 5 次，物理读取 0 次，预读 0 次
逻辑读取 (Logical Reads)：最关键的指标。指从内存（数据缓存）中读取的页数。逻辑读越少，说明查询效率越高。我们在对比两种写法哪个更优时，主要就看这个值
物理读取 (Physical Reads)：指直接从硬盘磁盘读取的页数。如果这个值很高，说明数据没在内存里，性能会很差
预读 (Read-ahead Reads)：SQL Server 为了加速查询，提前从磁盘加载到内存的页数

🗺️ SET SHOWPLAN… ON（看执行计划）

这个开关的作用是：让 SQL Server 只分析并返回执行计划，但不真正执行查询,它能告诉你数据库引擎打算“如何”去执行这条 SQL

SET SHOWPLAN_ALL ON; -- 或者使用 SET SHOWPLAN_TEXT ON
GO
-- 你的查询语句
SELECT * FROM Employees WHERE Department = '销售部';
GO
SET SHOWPLAN_ALL OFF;

输出结果解读:

执行后，你不会看到查询的数据结果，而是会看到一个树状的文本结构，告诉你：

操作类型：是走了“索引查找 (Index Seek)”还是全表“表扫描 (Table Scan)”？
预估成本：每一步操作的预估 CPU 和 I/O 成本是多少？
预估行数：优化器预估会返回多少行数据？

索引的维护

维护目标	作用	旧版 DBCC 命令 (已弃用)	现代标准做法 (推荐)
查看碎片	显示指定表或索引的碎片率和统计信息，用于判断是否需要维护	`DBCC SHOWCONTIG`	`sys.dm_db_index_physical_stats`
重建索引	删除旧索引并重新创建，最大程度消除碎片，优化页面密度（脱机操作）	`DBCC DBREINDEX`	`ALTER INDEX … REBUILD`
重组索引	对索引叶级页面进行重新排序，使物理顺序与逻辑顺序一致（联机操作）	`DBCC INDEXDEFRAG`	`ALTER INDEX … REORGANIZE`

全文索引

全文索引（Full-Text Index）是一种专门用于加速文本数据搜索的索引类型。它和普通的 B-Tree 索引完全不同

普通索引（B-Tree）：适合精确匹配和范围查询，比如 WHERE name = '张三'，但对 WHERE content LIKE '%数据库%' 这种模糊搜索无能为力，只能全表扫描
全文索引（倒排索引）：专门解决长文本的关键词搜索问题。它把文本拆分成一个个词（Token），然后建立"词 → 出现在哪些行"的映射关系，类似于书后面的"索引页"

⚙️ 功能安装

找到 SQL Server 安装介质,运行setup.exe
全新 SQL Server 独立安装或向现有安装添加功能
下一步直到找到向SQL Server现有实例中添加功能
去掉适用于SQL Server的Azure √
功能选择界面:全文和语义提取搜索✅

🛠️ 创建与使用要点

基本语法

-- 1. 先创建全文目录
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
 
-- CREATE FULLTEXT CATALOG 创建全文目录
-- ftCatalog  全文目录名称
-- AS DEFAULT  把它设为当前数据库的默认全文目录
 
-- 2. 创建全文索引（表必须有唯一非空列作为键）
CREATE FULLTEXT INDEX ON dbo.Articles(Content, Title)
-- 给 dbo.Articles 表的 Content 和 Title 列创建全文索引
 
KEY INDEX PK_Articles
-- 使用唯一键索引 PK_Articles 作为行标识(行标识就是通过全文索引筛选出来的一张详细对应表)
 
ON ftCatalog
-- 索引数据放在 ftCatalog 全文目录中
 
WITH CHANGE_TRACKING AUTO;
--数据变化时自动同步到全文索引
 
// 删除全文索引
 
DROP FULLTEXT INDEX ON 表名;

SQL事务

事务（Transaction）是数据库中不可分割的逻辑工作单元，它包含的一系列操作要么全部成功执行，要么全部不执行。

最经典的例子就是银行转账：A 账户扣款 100 元，B 账户增加 100 元，这两个操作必须作为一个整体成功或失败，不能出现"扣了钱但没到账"的情况

事务必须满足以下四个属性（简称 ACID）

原子性（Atomicity: 事务中的所有操作要么全部执行，要么全部不执行,如果事务执行过程中发生错误，所有已执行的操作都会被回滚，就像这个事务从未执行过一样
一致性（Consistency: 事务执行前后，数据库必须保持一致状态。所有约束、规则、触发器等都必须被满足。比如转账前后，两个账户的总金额不变
隔离性（Isolation）: 多个并发事务之间互不干扰,一个事务看到的要么是另一个事务修改前的数据，要么是修改后的数据，不会看到中间状态
持久性（Durability）: 事务一旦提交，对数据的修改就是永久性的，即使系统发生故障也不会丢失

三种事务模式

自动提交事务（默认模式）:每条 T-SQL 语句都是一个独立的事务，执行成功自动提交，失败自动回滚。这是 SQL Server 的默认行为。

-- 每条语句自动成为一个事务
INSERT INTO users VALUES ('张三', 25);
UPDATE users SET age = 26 WHERE name = '张三';

显式事务:开发者通过 BEGIN TRANSACTION 显式开启事务，通过 COMMIT 或 ROLLBACK 显式结束,这是最常用的模式

BEGIN TRANSACTION;
    UPDATE account SET balance = balance - 100 WHERE id = 1;
    UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT TRANSACTION;

隐式事务:开启后，SQL Server 会在前一个事务提交或回滚后，自动开启一个新事务，但每个事务仍需显式提交或回滚,需要通过 SET IMPLICIT_TRANSACTIONS ON 手动开启

SET IMPLICIT_TRANSACTIONS ON;
 
-- 以下操作自动开始新事务
INSERT INTO users VALUES ('李四', 30);
COMMIT TRANSACTION;  -- 显式提交
 
-- 下一个操作又会自动开始新事务
DELETE FROM users WHERE name = '李四';
ROLLBACK TRANSACTION;  -- 显式回滚

核心语法详解

BEGIN TRANSACTION — 开始事务: 标记事务的起点，将 @@TRANCOUNT 加 1

BEGIN TRANSACTION;
-- 或者简写
BEGIN TRAN;

COMMIT TRANSACTION — 提交事务

COMMIT TRANSACTION;
-- 或者简写
COMMIT TRAN;

ROLLBACK TRANSACTION — 回滚事务: 撤销事务中所有已执行的操作，恢复到事务开始前的状态

-- 回滚整个事务
ROLLBACK TRANSACTION;
 
-- 回滚到指定保存点
ROLLBACK TRANSACTION SavePoint1;

SAVE TRANSACTION — 设置保存点: 在事务内部设置一个标记点，可以有选择地回滚到该点，而不是回滚整个事务

BEGIN TRANSACTION;
    INSERT INTO demo VALUES ('AA', 'A term');
 
    SAVE TRANSACTION SavePoint1;  -- 设置保存点
 
    INSERT INTO demo VALUES ('BB', 'B term');
 
    ROLLBACK TRANSACTION SavePoint1;  -- 只回滚到保存点，AA 的插入保留
COMMIT TRANSACTION;

SET XACT_ABORT — 错误处理控制: 控制发生运行时错误时是否自动回滚整个事务

-- OFF（默认）：只回滚出错的语句，事务继续
SET XACT_ABORT OFF;
BEGIN TRAN;
    INSERT INTO t2 VALUES (1);   -- 成功
    INSERT INTO t2 VALUES (2);   -- 外键错误，只回滚本条
    INSERT INTO t2 VALUES (3);   -- 成功执行
COMMIT TRAN;  -- 1 和 3 被保存
 
-- ON：任何错误都回滚整个事务
SET XACT_ABORT ON;
BEGIN TRAN;
    INSERT INTO t2 VALUES (4);   -- 成功
    INSERT INTO t2 VALUES (5);   -- 外键错误，整个事务回滚
    INSERT INTO t2 VALUES (6);   -- 不会执行
COMMIT TRAN;  -- 所有修改都被撤销

在生产环境中建议始终设置 SET XACT_ABORT ON，确保错误时整个事务被回滚，避免数据不一致

嵌套事务:QL Server 支持嵌套事务，但需要注意：内部事务的 ROLLBACK 会回滚整个外部事务，而内部事务的 COMMIT 只是将 @@TRANCOUNT 减 1，并不会真正提交

BEGIN TRAN t1;
    INSERT INTO demo2 VALUES ('lis', 1);
 
    BEGIN TRAN t2;
        INSERT INTO demo VALUES ('BB', 'B term');
    COMMIT TRAN t2;  -- 只是 @@TRANCOUNT 减 1，并未真正提交
 
    INSERT INTO demo2 VALUES ('lis', 2);
COMMIT TRAN t1;  -- 这里才真正提交所有操作

分布式事务: 当事务需要跨多个 SQL Server 实例执行时，需要使用分布式事务，由 MS DTC（Microsoft 分布式事务协调器）管理，采用两阶段提交协议

BEGIN DISTRIBUTED TRANSACTION;
    UPDATE authors SET au_lname = 'McDonald' WHERE au_id = '409-56-7008';
    EXECUTE link_Server.pubs.dbo.change_lname '409-56-7008', 'McDonald';
COMMIT TRAN;

需要安装 MS DTC 服务，且链接服务器的 RPC 选项必须设为 True

🚩 带错误处理的转账事务

SET XACT_ABORT ON;
 
BEGIN TRY
    BEGIN TRANSACTION;
 
    -- A 账户扣款
    UPDATE account SET balance = balance - 100 WHERE id = 1;
 
    -- B 账户加款
    UPDATE account SET balance = balance + 100 WHERE id = 2;
 
    -- 记录转账日志
    INSERT INTO transfer_log (from_id, to_id, amount, transfer_time)
    VALUES (1, 2, 100, GETDATE());
 
    COMMIT TRANSACTION;
    PRINT '转账成功';
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
 
    PRINT '转账失败：' + ERROR_MESSAGE();
END CATCH;

锁

锁是 SQL Server 数据库引擎用来控制多个事务并发访问同一资源的机制。当多个用户同时操作数据库时，锁确保数据的一致性和完整性，防止出现脏读、丢失修改等问题

锁模式（Lock Modes）

SQL Server 使用多种锁模式来控制并发访问，每种模式适用于不同的操作场景

共享锁（S 锁）

用途：用于读操作（SELECT），不修改数据
特点：多个事务可以同时对同一资源持有 S 锁，即读读不互斥
释放时机：默认在读取完成后立即释放；如果隔离级别为可重复读或更高，则持有至事务结束
兼容性：与 S 锁兼容，与 X 锁、U 锁不兼容

排他锁（X 锁）

用途：用于数据修改操作（INSERT、UPDATE、DELETE）
特点：独占资源，其他事务既不能读也不能写（除非使用 NOLOCK 提示或读未提交隔离级别）
释放时机：持有至事务提交或回滚
兼容性：与所有锁都不兼容

更新锁（U 锁）

用途：用于 UPDATE 语句的"先读后写"阶段，防止死锁
特点：一次只有一个事务能获取 U 锁；U 锁与 S 锁兼容，但与 U 锁和 X 锁互斥
转换过程：先获取 U 锁读取数据 → 找到要修改的行后 → 将 U 锁升级为 X 锁进行修改

意向锁（Intent Locks）

意向锁是表级锁，用于建立锁的层次结构，表明事务打算在更低粒度（页或行）上加锁

意向锁类型	含义
IS（意向共享）	事务打算在下层资源上加 S 锁（读）
IX（意向排他）	事务打算在下层资源上加 X 锁（写）
SIX（共享意向排他）	事务在表上加 S 锁，同时打算在下层资源上加 X 锁

架构锁（Schema Locks）

锁类型	用途
Sch-M（架构修改锁）	执行 DDL 操作（如 `ALTER TABLE`）时使用，阻止所有访问
Sch-S（架构稳定性锁）	编译查询时使用，阻止 DDL 但不影响 S/U/X 锁

锁的监控与诊断

-- 查看当前所有锁
SELECT 
    request_session_id AS 会话ID,
    resource_type AS 资源类型,
    resource_description AS 资源描述,
    request_mode AS 锁模式,
    request_status AS 请求状态
FROM sys.dm_tran_locks;

¹⁾

加上 IF EXISTS 可以防止在存储过程不存在时报错，非常适合写在自动化的部署脚本里

²⁾

相当于电脑的任务计划