SQLServer常用指令手册

SQL Server 常用指令手册

覆盖数据库创建、表与约束、增删改查、查询进阶、索引、视图/存储过程/函数/触发器、事务、权限、运维排查。
适用版本：SQL Server 2012 及以上（标注 [2016+] [2017+] [2022] 的条目为对应版本起可用）。

---

目录

1. 连接与环境
2. 数据库操作
3. 数据类型
4. 表与约束
5. INSERT 插入
6. UPDATE 更新
7. DELETE 删除与 TRUNCATE
8. MERGE 合并（UPSERT）
9. SELECT 查询
10. 索引
11. 视图 / 存储过程 / 函数 / 触发器
12. 事务与并发控制
13. 登录、用户与权限
14. 常用系统查询与运维命令
15. 常见坑与最佳实践

---

1. 连接与环境

1.1 命令行连接

-- sqlcmd（Windows 集成认证）
sqlcmd -S localhost -E

-- SQL Server 身份认证
sqlcmd -S localhost -U sa -P YourPwd -d Sales

-- 指定实例（Express 版默认实例名）
sqlcmd -S .\SQLEXPRESS -E

-- 执行脚本文件
sqlcmd -S localhost -E -d Sales -i D:\script.sql -o D:\out.txt

-- 在会话中执行一条语句
sqlcmd -S localhost -E -d Sales -Q "SELECT @@VERSION"

1.2 ADO.NET 连接串

// Windows 认证
"Server=localhost;Database=Sales;Integrated Security=True;TrustServerCertificate=True;"
// SQL 认证
"Server=localhost;Database=Sales;User Id=sa;Password=YourPwd;TrustServerCertificate=True;"
// LocalDB
"Server=(localdb)\\MSSQLLocalDB;Database=Sales;Integrated Security=True;"

要点：

• TrustServerCertificate=True 用于规避新版驱动的证书校验失败；
• 大数据量批量写入用 SqlBulkCopy，比逐条 INSERT 快一到两个数量级；
• 连接串里 MultipleActiveResultSets=True 用于同一连接上并发读取多结果集。

1.3 会话上下文查看

SELECT @@VERSION;                -- 版本信息
SELECT @@SERVERNAME;             -- 实例名
SELECT DB_NAME();                -- 当前数据库
SELECT SUSER_SNAME();            -- 当前登录名
SELECT USER_NAME();              -- 当前数据库用户
SELECT @@SPID;                   -- 当前会话 ID
SELECT SYSDATETIME();            -- 当前时间（datetime2，精度高）

---

2. 数据库操作

2.1 创建数据库（完整参数）

CREATE DATABASE Sales
ON PRIMARY
(
    NAME        = Sales_Data,
    FILENAME    = 'D:\SQLData\Sales.mdf',
    SIZE        = 100MB,
    MAXSIZE     = UNLIMITED,
    FILEGROWTH  = 64MB
),
FILEGROUP Sales_FG_Archive
(
    NAME        = Sales_Archive,
    FILENAME    = 'D:\SQLData\Sales_Archive.ndf',
    SIZE        = 50MB,
    FILEGROWTH  = 32MB
)
LOG ON
(
    NAME        = Sales_Log,
    FILENAME    = 'D:\SQLLog\Sales_log.ldf',
    SIZE        = 32MB,
    FILEGROWTH  = 16MB
)
COLLATE Chinese_PRC_CI_AS;

最简写法（文件落在实例默认目录）：

CREATE DATABASE Demo;
CREATE DATABASE Demo COLLATE Chinese_PRC_CI_AS;

COLLATE 含义：Chinese_PRC_CI_AS = 中文简体的排序规则，CI（不区分大小写）、AS（区分重音）。中文库建议统一用此规则，避免之后字符串比较、排序、索引出现乱序问题。

2.2 修改数据库

-- 增加数据文件
ALTER DATABASE Sales
ADD FILE (NAME = Sales_Data2, FILENAME = 'D:\SQLData\Sales2.ndf',
          SIZE = 100MB, FILEGROWTH = 64MB) TO FILEGROUP Sales_FG_Archive;

-- 增加日志文件
ALTER DATABASE Sales
ADD LOG FILE (NAME = Sales_Log2, FILENAME = 'D:\SQLLog\Sales2.ldf',
              SIZE = 32MB, FILEGROWTH = 16MB);

-- 修改文件增长方式
ALTER DATABASE Sales MODIFY FILE (NAME = Sales_Data, FILEGROWTH = 128MB);

-- 改排序规则（库中已有表会报错，需先清空或导出重建）
ALTER DATABASE Sales COLLATE Chinese_PRC_CI_AS;

-- 只读 / 读写
ALTER DATABASE Sales SET READ_ONLY;
ALTER DATABASE Sales SET READ_WRITE;

-- 单用户模式（做还原、修复、离线操作时用）
ALTER DATABASE Sales SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE Sales SET MULTI_USER;

-- 修改兼容级别
ALTER DATABASE Sales SET COMPATIBILITY_LEVEL = 150;   -- 150 = SQL Server 2019

-- 自动收缩（生产环境建议关闭）
ALTER DATABASE Sales SET AUTO_SHRINK OFF;

-- 恢复模式：FULL / SIMPLE / BULKY_LOGGED
ALTER DATABASE Sales SET RECOVERY SIMPLE;

2.3 重命名

ALTER DATABASE Sales MODIFY NAME = SalesNew;

2.4 删除数据库

DROP DATABASE Sales;
DROP DATABASE IF EXISTS Sales;                 -- [2016+]
DROP DATABASE Sales WITH ROLLBACK IMMEDIATE;   -- 强制断开连接后删除（危险）

2.5 备份

-- 完整备份
BACKUP DATABASE Sales
TO DISK = 'D:\Backup\Sales_full_20261109.bak'
WITH INIT, COMPRESSION, CHECKSUM, STATS = 10;

-- 差异备份（依赖上一次完整备份）
BACKUP DATABASE Sales
TO DISK = 'D:\Backup\Sales_diff_20261109.bak'
WITH DIFFERENTIAL, COMPRESSION;

-- 事务日志备份（恢复模式必须为 FULL 或 BULK_LOGGED）
BACKUP LOG Sales
TO DISK = 'D:\Backup\Sales_log_20261109.trn'
WITH COMPRESSION;

-- 追加到已有媒体集（不加 INIT 即追加）
BACKUP DATABASE Sales TO DISK = 'D:\Backup\Sales_multi.bak';   -- 追加

查看备份文件内容：

RESTORE HEADERONLY FROM DISK = 'D:\Backup\Sales_full_20261109.bak';
RESTORE FILELISTONLY FROM DISK = 'D:\Backup\Sales_full_20261109.bak';  -- 逻辑文件名，还原时 MOVE 用
RESTORE VERIFYONLY FROM DISK = 'D:\Backup\Sales_full_20261109.bak';    -- 校验完整性

2.6 还原

-- 完整还原（覆盖现有库）
RESTORE DATABASE Sales
FROM DISK = 'D:\Backup\Sales_full_20261109.bak'
WITH REPLACE, RECOVERY,
     MOVE 'Sales_Data'  TO 'D:\SQLData\Sales.mdf',
     MOVE 'Sales_Log'   TO 'D:\SQLLog\Sales_log.ldf';

-- 差异还原（NORECOVERY 保留未完成状态，等日志还原）
RESTORE DATABASE Sales FROM DISK = 'D:\Backup\Sales_full.bak' WITH NORECOVERY, REPLACE;
RESTORE DATABASE Sales FROM DISK = 'D:\Backup\Sales_diff.bak' WITH NORECOVERY;

-- 日志还原 + 结束
RESTORE LOG Sales FROM DISK = 'D:\Backup\Sales_log.trn' WITH RECOVERY;

-- 从备份"另存"为新库
RESTORE DATABASE SalesCopy FROM DISK = 'D:\Backup\Sales_full.bak'
WITH RECOVERY,
     MOVE 'Sales_Data' TO 'D:\SQLData\SalesCopy.mdf',
     MOVE 'Sales_Log'  TO 'D:\SQLLog\SalesCopy_log.ldf';

2.7 分离与附加

EXEC sp_detach_db 'Sales';

CREATE DATABASE Sales ON
(FILENAME = 'D:\SQLData\Sales.mdf'),
(FILENAME = 'D:\SQLLog\Sales_log.ldf')
FOR ATTACH;

2.8 数据库快照 [2005+]

CREATE DATABASE Sales_Snap ON
(NAME = Sales_Data, FILENAME = 'D:\SQLData\Sales_Snap.ss')
AS SNAPSHOT OF Sales;

-- 回滚到快照（会覆盖当前库）
RESTORE DATABASE Sales FROM DATABASE_SNAPSHOT = 'Sales_Snap';

DROP DATABASE Sales_Snap;

2.9 查看数据库信息与大小

-- 所有数据库
SELECT name, database_id, state_desc, recovery_model_desc, collation_name, create_date
FROM sys.databases
ORDER BY database_id;

-- 当前库文件及大小（MB）
SELECT name, type_desc, size * 8 / 1024.0 AS size_mb, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('Sales');

-- 各表数据/索引占用（按对象统计）
SELECT  OBJECT_NAME(p.object_id) AS table_name,
        SUM(p.rows) AS row_count,
        SUM(a.total_pages) * 8 / 1024.0 AS total_mb
FROM sys.partitions p
JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE p.index_id IN (0, 1)
GROUP BY p.object_id
ORDER BY total_mb DESC;

---

3. 数据类型

3.1 数值

类型	字节	范围 / 说明
bit	1	0 / 1 / NULL，用于布尔
tinyint	1	0 ~ 255，无符号
smallint	2	-32768 ~ 32767
int	4	-21亿 ~ 21亿，最常用
bigint	8	±9.2e18，大 ID、时间戳（毫秒）
decimal(p,s) / numeric(p,s)	5~17	精确小数，p 总位数，s 小数位；金额用 decimal(18,2)
money / smallmoney	8 / 4	不推荐，精度易失真，用 decimal
float(n) / real	8 / 4	近似值，禁止用于金额和等值比较

3.2 字符

类型	说明
char(n)	定长，不足补空格，适合固定长度（如状态码、国家代码）
varchar(n)	变长，非 Unicode，英文/数字场景
varchar(max)	变长至 2GB，大文本
nchar(n) / nvarchar(n)	Unicode（中文必备），nvarchar 最常用
nvarchar(max)	大文本（超过 4000 字符/8000 字节）

n 的语义差异：nvarchar(n) 的 n 是字符数，最多 4000；varchar(n) 的 n 是字节数，最多 8000。中文一律用 nvarchar，用 varchar 存中文会在排序规则不匹配时出现乱码或查询匹配失败。

3.3 日期时间

类型	精度	范围	备注
date	天	0001-01-01 ~ 9999-12-31	只用日期
time(7)	100ns	00:00:00 ~ 23:59:59.9999999	只用时间
datetime	3.33ms	1753 ~ 9999	老类型，精度不稳，新表不要用
datetime2(0~7)	100ns	0001 ~ 9999	推荐，datetime2(0) 精确到秒
smalldatetime	1 分钟	1900 ~ 2079	精度低
datetimeoffset(7)	100ns	带时区偏移	跨时区存储

3.4 其他

类型	说明
uniqueidentifier	GUID，NEWID() 随机 / NEWSEQUENTIALID() 递增（推荐做聚集键）
binary(n) / varbinary(n) / varbinary(max)	二进制，存图片、文件流
xml	XML 类型，可 XQuery
sql_variant	任意类型（慎用，索引与比较受限）
rowversion (timestamp)	8 字节行版本号，用于并发检测，非时间
hierarchyid	层级路径（组织树）
geometry / geography	空间数据
sysname	nvarchar(128) 的别名，存对象名

---

4. 表与约束

4.1 创建表（含全部约束）

CREATE TABLE dbo.Device
(
    DeviceId    INT            IDENTITY(1,1) NOT NULL,
    DeviceNo    VARCHAR(32)    NOT NULL,
    DeviceName  NVARCHAR(100)  NOT NULL,
    Status      TINYINT        NOT NULL CONSTRAINT DF_Device_Status  DEFAULT (0),
    IpAddress   VARCHAR(15)    NULL,
    Remark      NVARCHAR(500)  NULL,
    CreatedAt   DATETIME2(0)   NOT NULL CONSTRAINT DF_Device_CreatedAt DEFAULT (SYSDATETIME()),
    UpdatedAt   DATETIME2(0)   NULL,
    RowVer      ROWVERSION     NOT NULL,

    CONSTRAINT PK_Device         PRIMARY KEY CLUSTERED (DeviceId),
    CONSTRAINT UQ_Device_DeviceNo UNIQUE (DeviceNo),
    CONSTRAINT CK_Device_Status  CHECK (Status BETWEEN 0 AND 3),
    CONSTRAINT CK_Device_Ip      CHECK (IpAddress IS NULL OR IpAddress LIKE '%.%.%.%')
);

4.2 六种约束

约束	关键字	作用
主键	PRIMARY KEY	唯一 + 非空，默认建聚集索引
外键	FOREIGN KEY	引用完整性
唯一	UNIQUE	唯一（允许多个 NULL）
检查	CHECK	值域校验
默认	DEFAULT	未赋值时的默认值
非空	NOT NULL	列级

4.3 外键（含级联）

ALTER TABLE dbo.Tag
ADD CONSTRAINT FK_Tag_Device
FOREIGN KEY (DeviceId) REFERENCES dbo.Device (DeviceId)
ON DELETE CASCADE      -- 主表删除时级联删除子表
ON UPDATE CASCADE;     -- 主键更新时级联更新（很少用）

-- 禁用 / 启用外键校验
ALTER TABLE dbo.Tag NOCHECK CONSTRAINT FK_Tag_Device;
ALTER TABLE dbo.Tag WITH CHECK CHECK CONSTRAINT FK_Tag_Device;

级联删除只支持一个层级链路上的单一路径，多路径会报 “may cause cycles or multiple cascade paths”。

4.4 标识列与序列

-- 标识列
CREATE TABLE T1 (Id INT IDENTITY(1,1) PRIMARY KEY, Name NVARCHAR(50));
-- 手动插入指定值（同一会话内生效，用完必须关）
SET IDENTITY_INSERT dbo.T1 ON;
INSERT INTO dbo.T1 (Id, Name) VALUES (100, N'手动');
SET IDENTITY_INSERT dbo.T1 OFF;

-- 重置种子
DBCC CHECKIDENT ('dbo.T1', RESEED, 0);   -- 下一个值为 1
DBCC CHECKIDENT ('dbo.T1', RESEED);      -- 查询当前值

-- 序列对象 [2012+]：可在多表共享，且可缓存
CREATE SEQUENCE dbo.Seq_OrderNo
    AS BIGINT START WITH 1 INCREMENT BY 1
    MINVALUE 1 MAXVALUE 999999999 CACHE 50;

SELECT NEXT VALUE FOR dbo.Seq_OrderNo;

4.5 计算列

-- 普通计算列（每次读取时计算）
AmountTax AS (Amount * TaxRate)

-- 持久化计算列（写入时计算并存储，可建索引）
AmountTotal AS (Amount * (1 + TaxRate)) PERSISTED

ALTER TABLE dbo.Order ADD AmountTotal AS (Amount * (1 + TaxRate)) PERSISTED;

4.6 修改表结构（ALTER TABLE）

-- 加列（带默认值，大表上 2012+ 可秒完成，但仍建议评估锁）
ALTER TABLE dbo.Device ADD Location NVARCHAR(50) NULL;
ALTER TABLE dbo.Device ADD Location NVARCHAR(50) NOT NULL
    CONSTRAINT DF_Device_Location DEFAULT (N'未分配');

-- 改列类型/长度/可空性
ALTER TABLE dbo.Device ALTER COLUMN Remark NVARCHAR(1000) NULL;

-- 删除列（先删依赖：索引、约束、默认）
ALTER TABLE dbo.Device DROP CONSTRAINT DF_Device_Location;
ALTER TABLE dbo.Device DROP COLUMN Location;

-- 加约束
ALTER TABLE dbo.Device ADD CONSTRAINT CK_Device_Ip2 CHECK (IpAddress IS NULL OR LEN(IpAddress) <= 15);
ALTER TABLE dbo.Device ADD CONSTRAINT DF_Device_Status2 DEFAULT (0) FOR Status;

-- 删约束（需要知道约束名）
ALTER TABLE dbo.Device DROP CONSTRAINT CK_Device_Status;

ALTER COLUMN 不能改列名，改列名用 sp_rename：

EXEC sp_rename 'dbo.Device.Remark', 'Memo', 'COLUMN';
EXEC sp_rename 'dbo.Device', 'DeviceInfo';       -- 改表名

4.7 删除表 / 清空表

DROP TABLE dbo.Device;
DROP TABLE IF EXISTS dbo.Device;                  -- [2016+]

TRUNCATE TABLE dbo.Device;                        -- 清空数据，重置 IDENTITY
DELETE FROM dbo.Device;                           -- 逐行删除，不重置 IDENTITY

DELETE vs TRUNCATE 对比

项目	DELETE	TRUNCATE
条件	支持 WHERE	不支持，整表清空
逐行 vs 整页	逐行，行级锁	释放数据页，表级锁（Sch-M）
事务日志	记录每行	只记录页释放，日志量小
触发触发器	触发	不触发
IDENTITY	不重置	重置为种子
可回滚	是	是（SQL Server 中在事务内可回滚）
权限	DELETE	ALTER
速度	慢（大表）	快

4.8 临时表与表变量

-- 本地临时表：仅当前会话可见，会话结束自动删除
CREATE TABLE #TempDevice (DeviceNo VARCHAR(32), Qty INT);
INSERT INTO #TempDevice VALUES ('D001', 10);
SELECT * FROM #TempDevice;
DROP TABLE #TempDevice;

-- 全局临时表：所有会话可见
CREATE TABLE ##GlobalTemp (Id INT);

-- 表变量：作用域限于批处理/存储过程，数据量小时用（无统计信息，易走错执行计划）
DECLARE @T TABLE (DeviceNo VARCHAR(32), Qty INT);
INSERT INTO @T VALUES ('D001', 10);
SELECT * FROM @T;

选择建议：行数不确定或较大（>1000）用 #临时表（有统计信息，可建索引）；小结果集、短生命周期用表变量。

4.9 时态表（系统版本化） [2016+]

CREATE TABLE dbo.Device2
(
    DeviceId  INT IDENTITY PRIMARY KEY,
    DeviceName NVARCHAR(100),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo   DATETIME2 GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Device2_History));

-- 查询历史某一时点
SELECT * FROM dbo.Device2 FOR SYSTEM_TIME AS OF '2026-11-01 08:00:00';

---

5. INSERT 插入

5.1 基本插入

INSERT INTO dbo.Device (DeviceNo, DeviceName, Status)
VALUES ('D001', N'一号机', 1);

5.2 多行插入

INSERT INTO dbo.Device (DeviceNo, DeviceName, Status)
VALUES ('D002', N'二号机', 1),
       ('D003', N'三号机', 0),
       ('D004', N'四号机', 2);

单条 VALUES 最多 1000 行；更多分批。

5.3 从查询结果插入

INSERT INTO dbo.Device (DeviceNo, DeviceName, Status)
SELECT DeviceNo, DeviceName, 1
FROM dbo.DeviceStaging
WHERE IsValid = 1;

5.4 SELECT INTO 建表并复制数据

SELECT DeviceNo, DeviceName, Status
INTO dbo.DeviceBak
FROM dbo.Device;

SELECT INTO 会新建表并复制数据，但不复制主键、索引、约束、默认值（只复制列定义和可空性）。

5.5 DEFAULT VALUES / 只插入部分列

INSERT INTO dbo.Device DEFAULT VALUES;        -- 全部列取默认值
INSERT INTO dbo.Device (DeviceNo) VALUES ('D005');   -- 其余列取默认值或 NULL

5.6 OUTPUT 返回插入结果

INSERT INTO dbo.Device (DeviceNo, DeviceName, Status)
OUTPUT inserted.DeviceId, inserted.DeviceNo, inserted.CreatedAt
VALUES ('D006', N'六号机', 1);

-- 插入并同时写审计表
INSERT INTO dbo.Device (DeviceNo, DeviceName)
OUTPUT inserted.DeviceId, inserted.DeviceNo, SYSDATETIME() INTO dbo.AuditLog (RefId, RefNo, OpTime)
VALUES ('D007', N'七号机');

5.7 大批量导入

-- BULK INSERT（服务端读取文件，服务账号需有文件权限）
BULK INSERT dbo.Device
FROM 'D:\Data\device.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR   = '\n',
    FIRSTROW        = 2,       -- 跳过表头
    CODEPAGE        = '65001', -- UTF-8
    TABLOCK,
    CHECK_CONSTRAINTS
);

-- 命令行 bcp 导出
-- bcp Sales.dbo.Device out D:\Data\device.csv -c -t"," -S localhost -T

CSV 中若含换行、逗号字段，需加 FIELDQUOTE，或改用 OPENROWSET / SqlBulkCopy。

5.8 忽略重复（幂等插入）

-- 方式一：先判断
IF NOT EXISTS (SELECT 1 FROM dbo.Device WHERE DeviceNo = 'D001')
    INSERT INTO dbo.Device (DeviceNo, DeviceName) VALUES ('D001', N'一号机');

-- 方式二：唯一索引 + IGNORE_DUP_KEY（重复行被丢弃，不报错）[2005+]
CREATE UNIQUE NONCLUSTERED INDEX UX_Device_DeviceNo
ON dbo.Device (DeviceNo) WITH (IGNORE_DUP_KEY = ON);

-- 方式三：TRY...CATCH 吞掉 2627/2601
BEGIN TRY
    INSERT INTO dbo.Device (DeviceNo, DeviceName) VALUES ('D001', N'一号机');
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() IN (2627, 2601) PRINT '已存在，跳过';
    ELSE THROW;
END CATCH

---

6. UPDATE 更新

6.1 基本更新

UPDATE dbo.Device
SET Status = 2, UpdatedAt = SYSDATETIME()
WHERE DeviceNo = 'D001';

6.2 多列与表达式

UPDATE dbo.Device
SET Status     = Status + 1,
    Remark     = ISNULL(Remark, N'') + N' 已巡检',
    UpdatedAt  = SYSDATETIME()
WHERE Status < 3;

6.3 关联更新（UPDATE … FROM）

UPDATE d
SET d.DeviceName = s.DeviceName,
    d.Status     = s.Status
FROM dbo.Device AS d
INNER JOIN dbo.DeviceStaging AS s ON d.DeviceNo = s.DeviceNo;

写 FROM 形式时给目标表起别名并在 UPDATE 别名 上更新，避免把 UPDATE 表名 与 JOIN 混用导致更新到错误的行。

6.4 UPDATE + 子查询

UPDATE dbo.Device
SET Status = (SELECT TOP 1 s.Status FROM dbo.DeviceStaging s WHERE s.DeviceNo = dbo.Device.DeviceNo)
WHERE EXISTS (SELECT 1 FROM dbo.DeviceStaging s WHERE s.DeviceNo = dbo.Device.DeviceNo);

6.5 带 OUTPUT

UPDATE dbo.Device
SET Status = 3
OUTPUT deleted.DeviceId, deleted.Status AS OldStatus, inserted.Status AS NewStatus
WHERE DeviceNo = 'D002';

6.6 条件更新（CASE）

UPDATE dbo.Device
SET Status = CASE
                WHEN Qty = 0 THEN 0
                WHEN Qty < 10 THEN 1
                ELSE 2
             END
WHERE IsActive = 1;

6.7 只更新前 N 行

UPDATE TOP (10) dbo.Device
SET Status = 1
WHERE Status = 0;

---

7. DELETE 删除与 TRUNCATE

7.1 基本删除

DELETE FROM dbo.Device WHERE DeviceNo = 'D001';
DELETE FROM dbo.Device WHERE Status = 3 AND CreatedAt < '2025-01-01';
DELETE TOP (100) FROM dbo.Device WHERE Status = 0;

7.2 关联删除

DELETE d
FROM dbo.Device AS d
INNER JOIN dbo.DeviceStaging AS s ON d.DeviceNo = s.DeviceNo
WHERE s.IsDeleted = 1;

7.3 带 OUTPUT（先看再删，或写审计）

DELETE dbo.Device
OUTPUT deleted.DeviceId, deleted.DeviceNo, SYSDATETIME() INTO dbo.DeletedLog
WHERE Status = 3;

7.4 大表分批删除（避免长事务锁表）

WHILE 1 = 1
BEGIN
    DELETE TOP (5000) FROM dbo.Log WHERE CreatedAt < DATEADD(DAY, -90, GETDATE());
    IF @@ROWCOUNT = 0 BREAK;
    -- 可选：WAITFOR DELAY '00:00:00.100';
END

7.5 软删除

-- 表加标记列
ALTER TABLE dbo.Device ADD IsDeleted BIT NOT NULL CONSTRAINT DF_Device_IsDel DEFAULT (0);
-- 删除即标记
UPDATE dbo.Device SET IsDeleted = 1 WHERE DeviceId = 1;
-- 查询时过滤
SELECT * FROM dbo.Device WHERE IsDeleted = 0;

---

8. MERGE 合并（UPSERT）

MERGE INTO dbo.Device AS t
USING (SELECT DeviceNo, DeviceName, Status FROM dbo.DeviceStaging) AS s
    ON t.DeviceNo = s.DeviceNo
WHEN MATCHED AND t.Status <> s.Status THEN
    UPDATE SET t.DeviceName = s.DeviceName, t.Status = s.Status, t.UpdatedAt = SYSDATETIME()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (DeviceNo, DeviceName, Status) VALUES (s.DeviceNo, s.DeviceName, s.Status)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action, inserted.DeviceId, deleted.DeviceId;

要点：

• 语句必须以分号结尾；
• 匹配条件列上应有唯一索引，否则可能出现重复匹配或意外行为；
• WHEN NOT MATCHED BY SOURCE 是危险操作（会删除目标表中所有未匹配行），生产环境慎用；
• 并发场景下 MERGE 存在已知竞态（可加 HOLDLOCK 串行化）。

---

9. SELECT 查询

9.1 语法骨架

SELECT   [TOP (n)]
         [列 | 表达式 | *]
FROM     表/视图
[WHERE   条件]
[GROUP BY 列]
[HAVING  聚合条件]
[ORDER BY 列 [ASC|DESC]]
[OFFSET  n ROWS FETCH NEXT m ROWS ONLY];

9.2 WHERE 常用运算符

WHERE Status = 1
WHERE Status IN (1, 2, 3)
WHERE Status NOT IN (0)
WHERE CreatedAt BETWEEN '2026-01-01' AND '2026-12-31'
WHERE DeviceName LIKE N'一号%'          -- % 任意多字符，_ 单字符，[] 字符集
WHERE DeviceNo LIKE 'D[0-9][0-9][0-9]'
WHERE Remark LIKE N'%50\%%' ESCAPE '\'  -- 查找含字面 % 的内容
WHERE Remark IS NULL
WHERE Remark IS NOT NULL
WHERE NOT EXISTS (SELECT 1 FROM ...)

NULL 陷阱：= NULL 永远不为真，必须用 IS NULL；NOT IN (含 NULL 的集合) 结果恒为空集。

9.3 TOP 与分页

SELECT TOP (10) * FROM dbo.Device ORDER BY CreatedAt DESC;
SELECT TOP (10) PERCENT * FROM dbo.Device;

-- 标准分页（推荐，2012+），必须带 ORDER BY
SELECT *
FROM dbo.Device
ORDER BY DeviceId
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;   -- 第 3 页，每页 10

9.4 排序

ORDER BY CreatedAt DESC, DeviceNo ASC;

-- 按指定顺序排列（例如状态优先级）
ORDER BY CASE Status WHEN 2 THEN 1 WHEN 0 THEN 2 ELSE 3 END;

-- 中文按拼音排序
ORDER BY DeviceName COLLATE Chinese_PRC_CI_AS;

9.5 分组与聚合

SELECT Status,
       COUNT(*)          AS Cnt,
       COUNT(DISTINCT DeviceNo) AS UniqCnt,
       SUM(Qty)          AS TotalQty,
       AVG(Qty)          AS AvgQty,
       MIN(CreatedAt)    AS FirstAt,
       MAX(CreatedAt)    AS LastAt
FROM dbo.Device
GROUP BY Status
HAVING COUNT(*) > 5;

汇总小计（ROLLUP / CUBE / GROUPING SETS）

SELECT Status, Region, SUM(Qty) AS Qty
FROM dbo.Sales
GROUP BY ROLLUP (Status, Region);        -- 逐级小计 + 总计

SELECT Status, Region, SUM(Qty) AS Qty
FROM dbo.Sales
GROUP BY CUBE (Status, Region);          -- 所有组合小计

SELECT Status, Region, SUM(Qty) AS Qty
FROM dbo.Sales
GROUP BY GROUPING SETS ((Status), (Region), ());   -- 指定小计组合

-- 用 GROUPING() 区分"真实数据"与"小计行"
SELECT CASE WHEN GROUPING(Status) = 1 THEN N'全部' ELSE CAST(Status AS NVARCHAR(10)) END AS StatusDesc,
       SUM(Qty)
FROM dbo.Sales
GROUP BY ROLLUP (Status);

9.6 连接（JOIN）

SELECT d.DeviceNo, d.DeviceName, t.TagName
FROM dbo.Device d
INNER JOIN dbo.Tag t      ON d.DeviceId = t.DeviceId;   -- 内连接：两边都有才出现
FROM dbo.Device d
LEFT  JOIN dbo.Tag t      ON d.DeviceId = t.DeviceId;   -- 左连接：左表全保留
FROM dbo.Device d
RIGHT JOIN dbo.Tag t      ON d.DeviceId = t.DeviceId;   -- 右连接
FROM dbo.Device d
FULL  JOIN dbo.Tag t      ON d.DeviceId = t.DeviceId;   -- 全连接
FROM dbo.Device d
CROSS JOIN dbo.Template   ON 1=1;                       -- 笛卡尔积（无 ON）

APPLY（相关连接，比 JOIN 灵活）

-- OUTER APPLY：类似 LEFT JOIN + 子查询，每行取一条匹配
SELECT d.DeviceNo, last.TagName, last.TagTime
FROM dbo.Device d
OUTER APPLY (
    SELECT TOP 1 t.TagName, t.TagTime
    FROM dbo.Tag t
    WHERE t.DeviceId = d.DeviceId
    ORDER BY t.TagTime DESC
) AS last;

-- CROSS APPLY：只保留有匹配的行（类似 INNER JOIN）

9.7 子查询

-- 标量子查询
SELECT DeviceNo,
       (SELECT COUNT(*) FROM dbo.Tag t WHERE t.DeviceId = d.DeviceId) AS TagCnt
FROM dbo.Device d;

-- IN / EXISTS
SELECT * FROM dbo.Device WHERE DeviceId IN (SELECT DeviceId FROM dbo.Tag);
SELECT * FROM dbo.Device d WHERE EXISTS (SELECT 1 FROM dbo.Tag t WHERE t.DeviceId = d.DeviceId);
SELECT * FROM dbo.Device d WHERE NOT EXISTS (SELECT 1 FROM dbo.Tag t WHERE t.DeviceId = d.DeviceId);

-- 派生表（子查询当表用）
SELECT x.Status, COUNT(*) FROM (SELECT Status FROM dbo.Device) AS x GROUP BY x.Status;

数据量大时优先 EXISTS / JOIN，NOT IN 在空值或大集合上性能差。

9.8 CTE（公用表表达式）

WITH cte AS (
    SELECT DeviceNo, Status, CreatedAt FROM dbo.Device WHERE Status = 1
)
SELECT * FROM cte WHERE CreatedAt > '2026-01-01';

递归 CTE（组织树、BOM、菜单层级）

WITH Org AS (
    SELECT Id, ParentId, Name, 0 AS Level
    FROM dbo.Department WHERE ParentId IS NULL          -- 根节点
    UNION ALL
    SELECT d.Id, d.ParentId, d.Name, o.Level + 1
    FROM dbo.Department d
    INNER JOIN Org o ON d.ParentId = o.Id               -- 递归部分
)
SELECT * FROM Org
ORDER BY Level, Id
OPTION (MAXRECURSION 0);                                 -- 0 = 不限制深度（默认 100）

9.9 集合运算

SELECT DeviceNo FROM dbo.DeviceA
UNION      SELECT DeviceNo FROM dbo.DeviceB;   -- 合并并去重
SELECT DeviceNo FROM dbo.DeviceA
UNION ALL  SELECT DeviceNo FROM dbo.DeviceB;   -- 合并保留重复（快）
SELECT DeviceNo FROM dbo.DeviceA
INTERSECT  SELECT DeviceNo FROM dbo.DeviceB;   -- 交集
SELECT DeviceNo FROM dbo.DeviceA
EXCEPT     SELECT DeviceNo FROM dbo.DeviceB;   -- 差集（A 有 B 没有）

9.10 窗口函数（分析函数）

-- 行号、排名
SELECT DeviceNo, Qty,
       ROW_NUMBER()  OVER (PARTITION BY Status ORDER BY Qty DESC) AS Rn,       -- 连续序号
       RANK()        OVER (PARTITION BY Status ORDER BY Qty DESC) AS Rk,       -- 并列跳号
       DENSE_RANK()  OVER (PARTITION BY Status ORDER BY Qty DESC) AS Drk,      -- 并列不跳号
       NTILE(4)      OVER (ORDER BY Qty DESC)                     AS Quartile  -- 分 4 组
FROM dbo.Device;

-- 分组内取第一条（去重、取最新记录的常用写法）
WITH x AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY DeviceNo ORDER BY CreatedAt DESC) AS rn
    FROM dbo.DeviceLog
)
SELECT * FROM x WHERE rn = 1;

-- 相邻行取值
SELECT TagTime, Value,
       LAG(Value, 1)  OVER (ORDER BY TagTime) AS PrevValue,
       LEAD(Value, 1) OVER (ORDER BY TagTime) AS NextValue,
       FIRST_VALUE(Value) OVER (PARTITION BY DeviceId ORDER BY TagTime) AS FirstVal,
       LAST_VALUE(Value)  OVER (PARTITION BY DeviceId ORDER BY TagTime
                                ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastVal
FROM dbo.Tag;

-- 累计与移动平均
SELECT TagTime, Value,
       SUM(Value) OVER (PARTITION BY DeviceId ORDER BY TagTime
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal,
       AVG(Value) OVER (ORDER BY TagTime ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS MA3
FROM dbo.Tag;

-- 占比
SELECT DeviceNo, Qty,
       Qty * 1.0 / SUM(Qty) OVER (PARTITION BY Status) AS Ratio
FROM dbo.Device;

9.11 PIVOT / UNPIVOT

-- 行转列
SELECT *
FROM (
    SELECT DeviceNo, TagName, Value FROM dbo.Tag
) AS src
PIVOT (
    SUM(Value) FOR TagName IN ([温度], [压力], [转速])
) AS pvt;

-- 列转行
SELECT DeviceNo, TagName, Value
FROM dbo.TagWide
UNPIVOT (
    Value FOR TagName IN ([温度], [压力], [转速])
) AS unpvt;

PIVOT 的列名必须写死（不能动态），动态列需要拼 SQL 后 EXEC sp_executesql。

9.12 CASE / IIF / CHOOSE

SELECT CASE
           WHEN Qty = 0      THEN N'停机'
           WHEN Qty < 10     THEN N'低负荷'
           WHEN Qty BETWEEN 10 AND 80 THEN N'正常'
           ELSE N'超限'
       END AS LevelDesc
FROM dbo.Device;

SELECT IIF(Qty > 10, N'正常', N'异常') AS Flag FROM dbo.Device;        -- 只能两个分支
SELECT CHOOSE(Status + 1, N'离线', N'运行', N'报警', N'维护') AS StatDesc FROM dbo.Device;

9.13 常用函数速查

字符串

函数	说明 / 示例
LEN(s) / DATALENGTH(s)	字符数（不含尾空格）/ 字节数
LEFT(s,n) / RIGHT(s,n)	取左/右 n 个字符
SUBSTRING(s,start,len)	子串（start 从 1 开始）
CHARINDEX('a', s)	查找位置，未找到返回 0
REPLACE(s,'a','b')	替换
STUFF(s,start,len,new)	删除并插入
TRIM(s) [2017+]	去两端空格；旧版本用 LTRIM(RTRIM(s))
UPPER / LOWER	大小写
CONCAT(a,b,...)	拼接，NULL 视为空串
CONCAT_WS('-', a, b) [2017+]	带分隔符拼接，跳过 NULL
STRING_AGG(col, ',') [2017+]	分组内拼接成一行
STRING_SPLIT(s, ',') [2016+]	拆分为行
REPLICATE(s,n) / SPACE(n)	重复 / 生成空格
FORMAT(val, 'yyyy-MM-dd')	格式化，性能差，勿在大数据量查询中用

-- 分组拼接示例
SELECT DeviceNo, STRING_AGG(TagName, N'、') WITHIN GROUP (ORDER BY TagName) AS Tags
FROM dbo.Tag GROUP BY DeviceNo;

日期

函数	说明
GETDATE()	当前时间，返回 datetime（旧）
SYSDATETIME()	当前时间，返回 datetime2（推荐）
GETUTCDATE() / SYSUTCDATETIME()	UTC 时间
DATEADD(part, n, date)	加减，part：year/month/day/hour/minute/second
DATEDIFF(part, a, b)	差值
DATEDIFF_BIG(part, a, b)	差值（毫秒级大数）
DATEFROMPARTS(y,m,d) [2012+]	由年月日构造
EOMONTH(date) [2012+]	月末日期
DATEPART(part, date) / YEAR/MONTH/DAY	取部分
DATENAME(part, date)	取名称（如月份英文名/星期）
ISDATE(s)	是否可转日期

SELECT DATEADD(DAY, -7, GETDATE())            AS 一周前;
SELECT DATEDIFF(SECOND, StartAt, EndAt)       AS 耗时秒;
SELECT EOMONTH(GETDATE())                     AS 本月末;
SELECT CONVERT(VARCHAR(10), GETDATE(), 120)   AS 日期;      -- 2026-11-09
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd HH:mm:ss');
-- 常用样式码：112=yyyyMMdd，120=yyyy-MM-dd HH:mm:ss，126=ISO8601

类型转换

CAST(x AS INT)
CONVERT(INT, x)
CONVERT(VARCHAR(10), GETDATE(), 112)
TRY_CAST(x AS INT)        -- 失败返回 NULL，不报错
TRY_CONVERT(INT, x)       -- 同上
TRY_PARSE('2026-11-09' AS DATE)
PARSE('2026-11-09' AS DATE USING 'zh-CN')

逻辑与判断

ISNULL(x, 0)                     -- 两参数，返回类型取第一参数
COALESCE(a, b, c)                -- 多参数，返回第一个非 NULL
NULLIF(a, b)                     -- 相等返回 NULL

聚合

COUNT(*) / COUNT(col) / COUNT(DISTINCT col)
SUM / AVG / MIN / MAX
STDEV / VAR                       -- 标准差 / 方差

---

10. 索引

10.1 两类索引

类型	数量	说明
聚集索引（Clustered）	每表 1 个	决定数据物理顺序，通常是主键
非聚集索引（Nonclustered）	多个	独立结构，叶子节点存键 + 行定位符

10.2 创建

-- 聚集索引（若表已有主键则已存在）
CREATE CLUSTERED INDEX CX_Device_DeviceId ON dbo.Device (DeviceId);

-- 非聚集索引 + 包含列（覆盖查询，避免回表）
CREATE NONCLUSTERED INDEX IX_Device_Status_CreatedAt
ON dbo.Device (Status, CreatedAt)
INCLUDE (DeviceNo, DeviceName)
WITH (FILLFACTOR = 90);

-- 唯一索引
CREATE UNIQUE NONCLUSTERED INDEX UX_Device_DeviceNo ON dbo.Device (DeviceNo);

-- 筛选索引（只索引部分行，省空间）
CREATE NONCLUSTERED INDEX IX_Device_Active
ON dbo.Device (Status) WHERE IsDeleted = 0;

-- 线上重建（ONLINE=ON 需企业版；标准版会锁表）
CREATE NONCLUSTERED INDEX IX_Device_Status
ON dbo.Device (Status) WITH (ONLINE = ON, DROP_EXISTING = ON, MAXDOP = 4);

10.3 维护

-- 查看索引
EXEC sp_helpindex 'dbo.Device';

-- 删除
DROP INDEX IX_Device_Status ON dbo.Device;
DROP INDEX IF EXISTS IX_Device_Status ON dbo.Device;     -- [2016+]

-- 禁用 / 启用
ALTER INDEX IX_Device_Status ON dbo.Device DISABLE;
ALTER INDEX ALL ON dbo.Device REBUILD;
ALTER INDEX ALL ON dbo.Device REORGANIZE;

-- 索引碎片
SELECT OBJECT_NAME(i.object_id) AS TableName, i.name AS IndexName, s.avg_fragmentation_in_percent, s.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.avg_fragmentation_in_percent > 10 AND s.page_count > 100;

碎片 > 30% 重建（REBUILD），10%~30% 重组（REORGANIZE）。

10.4 缺失索引建议 [2005+]

SELECT TOP 20
       ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans), 0) AS Score,
       d.statement AS TableName, d.equality_columns, d.inequality_columns, d.included_columns
FROM sys.dm_db_missing_index_group_stats s
JOIN sys.dm_db_missing_index_groups g ON s.group_handle = g.index_group_handle
JOIN sys.dm_db_missing_index_details d ON g.index_handle = d.index_handle
ORDER BY Score DESC;

只是"候选建议"，需结合写入频率与现有索引合并后再建。

---

11. 视图 / 存储过程 / 函数 / 触发器

11.1 视图

CREATE VIEW dbo.vw_ActiveDevice
AS
SELECT d.DeviceId, d.DeviceNo, d.DeviceName, d.Status, t.TagName
FROM dbo.Device d
LEFT JOIN dbo.Tag t ON d.DeviceId = t.DeviceId
WHERE d.IsDeleted = 0;
GO

SELECT * FROM dbo.vw_ActiveDevice;

ALTER VIEW dbo.vw_ActiveDevice AS SELECT ...;   -- 修改
DROP VIEW dbo.vw_ActiveDevice;
DROP VIEW IF EXISTS dbo.vw_ActiveDevice;        -- [2016+]

-- 查看视图定义
EXEC sp_helptext 'dbo.vw_ActiveDevice';
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.vw_ActiveDevice'));

视图不提升性能，只是保存的查询；多表连接视图上的写入往往受限制。

索引视图（物化，能提升聚合查询性能）

CREATE VIEW dbo.vw_DeviceStat
WITH SCHEMABINDING
AS
SELECT DeviceId, COUNT_BIG(*) AS Cnt, SUM(Qty) AS TotalQty
FROM dbo.Device
GROUP BY DeviceId;
GO
CREATE UNIQUE CLUSTERED INDEX CX_vw_DeviceStat ON dbo.vw_DeviceStat (DeviceId);

索引视图要求 WITH SCHEMABINDING，且查询必须使用 NOEXPAND 提示才保证走索引。

11.2 存储过程

CREATE PROCEDURE dbo.usp_GetDevice
    @Status TINYINT = NULL,
    @TopN   INT = 100
AS
BEGIN
    SET NOCOUNT ON;

    SELECT TOP (@TopN) DeviceNo, DeviceName, Status, CreatedAt
    FROM dbo.Device
    WHERE (@Status IS NULL OR Status = @Status)
      AND IsDeleted = 0
    ORDER BY CreatedAt DESC;
END;
GO

-- 调用
EXEC dbo.usp_GetDevice @Status = 1, @TopN = 20;
EXEC dbo.usp_GetDevice;                       -- 全部默认参数

-- 查看 / 修改 / 删除
EXEC sp_helptext 'dbo.usp_GetDevice';
SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.usp_GetDevice'));
ALTER PROCEDURE dbo.usp_GetDevice ...;
DROP PROCEDURE dbo.usp_GetDevice;
DROP PROCEDURE IF EXISTS dbo.usp_GetDevice;

-- 查看参数
SELECT name, parameter_id, type_name, max_length, is_output
FROM sys.parameters WHERE object_id = OBJECT_ID('dbo.usp_GetDevice');

带输出参数与返回码

CREATE PROCEDURE dbo.usp_AddDevice
    @DeviceNo   VARCHAR(32),
    @DeviceName NVARCHAR(100),
    @NewId      INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.Device (DeviceNo, DeviceName) VALUES (@DeviceNo, @DeviceName);
    SET @NewId = SCOPE_IDENTITY();
    RETURN 0;
END;

DECLARE @id INT;
EXEC dbo.usp_AddDevice 'D100', N'测试', @id OUTPUT;
SELECT @id;

动态 SQL（参数化，防注入）

CREATE PROCEDURE dbo.usp_SearchDevice
    @Keyword NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @sql NVARCHAR(MAX) = N'
        SELECT DeviceNo, DeviceName FROM dbo.Device
        WHERE DeviceName LIKE @kw + N''%''';

    EXEC sp_executesql @sql, N'@kw NVARCHAR(50)', @kw = @Keyword;
END;

动态 SQL 一律用 sp_executesql + 参数，不要字符串拼接用户输入。

11.3 函数

-- 标量函数
CREATE FUNCTION dbo.fn_DeviceLevel (@Qty INT)
RETURNS NVARCHAR(10)
AS
BEGIN
    RETURN CASE WHEN @Qty = 0 THEN N'停机'
                WHEN @Qty < 10 THEN N'低负荷'
                ELSE N'正常' END;
END;
GO
SELECT dbo.fn_DeviceLevel(5);

-- 内联表值函数（性能好，可被优化器展开）
CREATE FUNCTION dbo.fn_DeviceByStatus (@Status TINYINT)
RETURNS TABLE
AS
RETURN
(
    SELECT DeviceNo, DeviceName, Status FROM dbo.Device WHERE Status = @Status
);
GO
SELECT * FROM dbo.fn_DeviceByStatus(1);

-- 多语句表值函数（性能较差）
CREATE FUNCTION dbo.fn_Report (@Status TINYINT)
RETURNS @Result TABLE (DeviceNo VARCHAR(32), Cnt INT)
AS
BEGIN
    INSERT INTO @Result
    SELECT DeviceNo, COUNT(*) FROM dbo.Device WHERE Status = @Status GROUP BY DeviceNo;
    RETURN;
END;
GO

-- 删除
DROP FUNCTION dbo.fn_DeviceLevel;
DROP FUNCTION IF EXISTS dbo.fn_DeviceLevel;

标量函数在 WHERE/SELECT 中逐行调用会严重拖慢大表查询，能写成 CASE 就别用函数。

11.4 触发器

-- DML 触发器（AFTER）
CREATE TRIGGER trg_Device_Update
ON dbo.Device
AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    IF TRIGGER_NESTLEVEL() > 2 RETURN;      -- 防递归

    INSERT INTO dbo.AuditLog (RefId, OpTime, OpType)
    SELECT DeviceId, SYSDATETIME(), 'UPDATE' FROM inserted;
END;
GO

-- INSTEAD OF 触发器（可用于视图写操作）
CREATE TRIGGER trg_vw_Device_Insert
ON dbo.vw_ActiveDevice
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO dbo.Device (DeviceNo, DeviceName, Status)
    SELECT DeviceNo, DeviceName, Status FROM inserted;
END;
GO

-- 禁用 / 启用 / 删除
ALTER TABLE dbo.Device DISABLE TRIGGER trg_Device_Update;
ALTER TABLE dbo.Device ENABLE TRIGGER trg_Device_Update;
DROP TRIGGER trg_Device_Update;
DROP TRIGGER IF EXISTS trg_Device_Update;

-- 查看触发器
SELECT name, is_disabled, is_instead_of_trigger FROM sys.triggers WHERE parent_id = OBJECT_ID('dbo.Device');

触发器里用 inserted / deleted 虚拟表；inserted 在 INSERT/UPDATE 有，deleted 在 DELETE/UPDATE 有。触发器内的失败会回滚整个外层语句，谨慎放耗时逻辑。

---

12. 事务与并发控制

12.1 显式事务

BEGIN TRANSACTION;

BEGIN TRY
    UPDATE dbo.Device SET Status = 2 WHERE DeviceId = 1;
    INSERT INTO dbo.AuditLog (RefId, OpTime, OpType) VALUES (1, SYSDATETIME(), 'UPDATE');

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

    -- 重新抛出，保留原始错误信息
    THROW;
END CATCH;

12.2 嵌套与保存点

BEGIN TRAN;                    -- @@TRANCOUNT = 1
SAVE TRANSACTION sp1;
    UPDATE ...;
ROLLBACK TRANSACTION sp1;      -- 回滚到保存点，事务仍存活
COMMIT TRAN;                   -- 提交
-- 注意：嵌套 BEGIN TRAN 只是计数 +1，内层 COMMIT 实际不提交，必须最外层 COMMIT

12.3 常用会话设置

SET NOCOUNT ON;          -- 不返回"受影响行数"，减少网络往返（存储过程必加）
SET XACT_ABORT ON;       -- 出错自动回滚整个事务（推荐）
SET ANSI_NULLS ON;       -- 现代默认
SET ARITHABORT ON;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- 默认级别

12.4 隔离级别

级别	脏读	不可重复读	幻读	说明
READ UNCOMMITTED	有	有	有	最快，可配合 NOLOCK
READ COMMITTED	无	有	有	默认级别
REPEATABLE READ	无	无	有	加共享锁到事务结束
SERIALIZABLE	无	无	无	最严，最慢
SNAPSHOT	无	无	无	行版本，需库级开启，读不阻塞写

-- 库级开启快照隔离（需独占访问）
ALTER DATABASE Sales SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

12.5 锁提示（谨慎使用）

SELECT * FROM dbo.Device WITH (NOLOCK);            -- 不加锁读，可能脏读/漏行
SELECT * FROM dbo.Device WITH (READPAST);          -- 跳过被锁行（队列取任务常用）
SELECT * FROM dbo.Device WITH (UPDLOCK, ROWLOCK);  -- 更新锁 + 行锁
UPDATE dbo.Device WITH (ROWLOCK) SET ... ;         -- 避免锁升级为表锁
SELECT * FROM dbo.Device WITH (TABLOCKX);          -- 排他表锁（慎用）

NOLOCK 在关键业务（金额、库存、工单状态）上不要用，宁可加合适索引减少阻塞。

12.6 死锁排查

-- 当前会话与被阻塞情况
SELECT r.session_id, r.blocking_session_id, r.wait_type, r.wait_time,
       r.status, r.command, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.blocking_session_id <> 0;

-- 查看等待任务
SELECT * FROM sys.dm_os_waiting_tasks WHERE blocking_session_id IS NOT NULL;

-- 开启死锁图形化记录（跟踪标志 1222）
DBCC TRACEON (1222, -1);
DBCC TRACEOFF(1222, -1);

减少死锁的通用手段：统一多表访问顺序、缩短事务、避免在事务内做远程调用/UI 交互、必要时加 UPDLOCK 提前占锁。

---

13. 登录、用户与权限

13.1 登录与用户

-- 服务器级登录（SQL 认证）
CREATE LOGIN app_login WITH PASSWORD = N'P@ssw0rd!2026', DEFAULT_DATABASE = Sales;
CREATE LOGIN app_login FROM WINDOWS;            -- Windows 认证

-- 数据库级用户（映射登录）
CREATE USER app_user FOR LOGIN app_login WITH DEFAULT_SCHEMA = dbo;
CREATE USER app_user FROM LOGIN app_login;      -- 简写
CREATE USER app_user WITHOUT LOGIN;             -- 无登录的用户（用于沙箱场景）

-- 改密码
ALTER LOGIN app_login WITH PASSWORD = N'NewP@ssw0rd!';

-- 删除
DROP USER app_user;
DROP LOGIN app_login;

13.2 授权

-- 表级
GRANT SELECT ON dbo.Device TO app_user;
GRANT INSERT, UPDATE, DELETE ON dbo.Device TO app_user;
DENY  DELETE ON dbo.Device TO app_user;          -- DENY 优先级高于 GRANT

-- 架构级（推荐，一次覆盖架构下所有对象）
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO app_user;

-- 存储过程执行权限
GRANT EXECUTE ON dbo.usp_GetDevice TO app_user;
GRANT EXECUTE ON SCHEMA::dbo TO app_user;

-- 撤销
REVOKE SELECT ON dbo.Device FROM app_user;

-- 查看权限
SELECT * FROM fn_my_permissions(NULL, 'DATABASE');
SELECT * FROM fn_my_permissions('dbo.Device', 'OBJECT');

13.3 内置角色

角色	权限范围
db_owner	库内最高
db_datareader	读所有表
db_datawriter	写所有表
db_ddladmin	改结构，不能改数据
db_backupoperator	备份
db_denydatareader / db_denydatawriter	拒绝读/写
sysadmin	服务器级最高（服务器角色）

ALTER ROLE db_datareader ADD MEMBER app_user;
ALTER ROLE db_datawriter ADD MEMBER app_user;
ALTER ROLE db_owner ADD MEMBER app_user;
ALTER SERVER ROLE sysadmin ADD MEMBER app_login;

生产环境给应用账号最小权限：一般 db_datareader + db_datawriter 或按表 GRANT，不要给 sa 或 db_owner。

---

14. 常用系统查询与运维命令

14.1 系统存储过程

命令	作用
sp_help 'dbo.Device'	表结构、约束、索引一览
sp_helpdb / sp_helpdb 'Sales'	数据库列表 / 单库详情
sp_helptext 'dbo.usp_X'	存储过程/视图/函数/触发器定义
sp_helpindex 'dbo.Device'	索引列表
sp_helpconstraint 'dbo.Device'	约束列表
sp_who2	所有会话、CPU/内存、阻塞情况
sp_lock	当前锁信息
sp_spaceused / sp_spaceused 'dbo.Device'	库/表空间占用
sp_rename	重命名表/列/索引
sp_configure	服务器配置查看与修改
sp_addlinkedserver	建立链接服务器
sp_detach_db / sp_attach_db	分离 / 附加

EXEC sp_help 'dbo.Device';
EXEC sp_who2;
EXEC sp_spaceused;
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'max degree of parallelism', 4; RECONFIGURE;

14.2 目录视图查询（对象结构）

-- 当前库所有表
SELECT SCHEMA_NAME(schema_id) AS sch, name AS table_name, create_date, modify_date
FROM sys.tables ORDER BY sch, table_name;

-- 表的所有列
SELECT c.column_id, c.name AS column_name, t.name AS data_type,
       c.max_length, c.precision, c.scale, c.is_nullable, c.is_identity, c.is_computed
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.Device')
ORDER BY c.column_id;

-- 所有约束
SELECT name, type_desc, is_disabled FROM sys.check_constraints WHERE parent_object_id = OBJECT_ID('dbo.Device');
SELECT name, type_desc FROM sys.default_constraints WHERE parent_object_id = OBJECT_ID('dbo.Device');
SELECT name, type_desc FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('dbo.Device');

-- 所有存储过程 / 视图 / 函数
SELECT name, type_desc, create_date, modify_date FROM sys.procedures;
SELECT name, type_desc FROM sys.views;
SELECT name, type_desc FROM sys.objects WHERE type IN ('FN','IF','TF');

-- 某列被哪些对象引用（改表前排查依赖）
SELECT referencing_entity_name, referencing_class_desc
FROM sys.dm_sql_referencing_entities('dbo.Device', 'OBJECT');

14.3 性能与执行计划

-- 打开 IO / 时间统计（客户端输出）
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT ... ;
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

-- 只估算不执行（查执行计划，安全）
SET SHOWPLAN_ALL ON;
SELECT ... ;
SET SHOWPLAN_ALL OFF;

-- 返回 XML 执行计划
SET STATISTICS XML ON;

-- 缓存中的执行计划（找最耗 CPU 的语句）
SELECT TOP 20
       qs.total_worker_time / qs.execution_count AS avg_cpu,
       qs.execution_count, qs.total_elapsed_time / qs.execution_count AS avg_elapsed,
       SUBSTRING(t.text, qs.statement_start_offset / 2 + 1,
                 (CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
                  ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS stmt,
       p.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) p
ORDER BY avg_cpu DESC;

-- 清缓存（仅测试环境）
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

14.4 健康检查

-- 数据一致性检查（重活，建议低峰期）
DBCC CHECKDB ('Sales') WITH NO_INFOMSGS, ALL_ERRORMSGS;
DBCC CHECKDB ('Sales') WITH PHYSICAL_ONLY;      -- 只查物理结构，快

-- 收缩文件（不建议常规使用，会加剧碎片）
DBCC SHRINKFILE (Sales_Data, 100);              -- 收缩到 100MB
DBCC SHRINKDATABASE (Sales, 10);                 -- 保留 10% 空闲

-- 查看当前活动事务
SELECT transaction_id, name, transaction_begin_time, transaction_type
FROM sys.dm_tran_active_transactions;

-- 查看数据库恢复进度（还原中）
SELECT percent_complete, start_time, estimated_completion_time, command
FROM sys.dm_exec_requests WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE');

14.5 常用服务端配置

-- 查看所有配置
EXEC sp_configure;

-- 最大并行度、内存、连接数
EXEC sp_configure 'max degree of parallelism', 4; RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 8192; RECONFIGURE;
EXEC sp_configure 'user connections', 0; RECONFIGURE;

-- 查看版本/补丁
SELECT SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('ProductLevel'), SERVERPROPERTY('Edition');

---

15. 常见坑与最佳实践

1. NULL 比较：= NULL、<> NULL 永远为假，用 IS NULL / IS NOT NULL。
2. 字符串长度与中文：中文列一律 NVARCHAR，字面量用 N'中文'；VARCHAR(10) 存不下 10 个汉字（中文每字 2 字节以上）。
3. 日期比较用范围，不用函数：

   -- 错误写法（无法走索引）
   WHERE YEAR(CreatedAt) = 2026 AND MONTH(CreatedAt) = 11
   -- 正确写法
   WHERE CreatedAt >= '2026-11-01' AND CreatedAt < '2026-12-01'
   

4. datetime 精度问题：datetime 约 3.33ms 一步，GETDATE() 两端边界容易漏行；新表统一用 datetime2(0~3)。
5. GETDATE() vs SYSDATETIME()：后者精度高、类型新，推荐。
6. 隐式类型转换导致索引失效：DeviceNo 是 varchar，却传 nvarchar 参数或 int 值，会做列转换后扫描全表。参数类型必须与列类型一致。
7. SELECT *：增加 IO、破坏覆盖索引、加列后客户端可能报错，查询明确列出所需列。
8. NOLOCK 不是性能银弹：可能读到脏数据、漏行、重复行；正确做法是加索引 / 缩短事务。
9. 事务内不要等待外部：不要在 BEGIN TRAN 之间做 HTTP 调用、弹窗、Thread.Sleep，会长时间持锁。
10. 存储过程加 SET NOCOUNT ON：否则每次执行多返回一个结果集，ADO.NET 里容易错位。
11. @@IDENTITY / SCOPE_IDENTITY() / IDENT_CURRENT：优先 SCOPE_IDENTITY()（当前作用域）；@@IDENTITY 会被触发器污染。
12. IDENTITY 不保证连续：并发、回滚、重启都会跳号；业务单号不要依赖 IDENTITY 连续，需要时用 SEQUENCE 或自己生成。
13. 大表加非空默认列 / 建索引：会长时间锁表，放在维护窗口，或先用可空列 + 分批回填 + 再加约束。
14. MERGE 结尾必须有分号，且匹配列要有唯一索引。
15. 比较 float/money：浮点不要做等值比较，金额用 decimal(18,2)。
16. ORDER BY 缺失导致分页结果抖动：OFFSET-FETCH 必须配唯一且稳定的排序列（如 Id）。
17. 索引太多拖慢写入：每多一个索引，INSERT/UPDATE 多一次维护成本；按实际查询建，定期清理无用索引。
18. TRUNCATE 需要 ALTER 权限，且不能用于有外键引用的表。
19. 跨库/跨服务器查询：SELECT * FROM OtherDb.dbo.T（同实例）；跨实例先 sp_addlinkedserver 建链接服务器，或用 OPENROWSET。
20. sa 账号：生产环境禁用或改强密码，应用一律用独立最小权限账号。

连接字符串与常见错误对应

错误	常见原因
Login failed for user	账号密码错误 / SQL 认证未启用 / 用户未映射到库
Cannot open database ... requested by the login	登录存在但库中无对应用户，或库处于单用户/离线
A network-related or instance-specific error	实例名错、TCP/IP 未启用、防火墙挡 1433、SQL Browser 未启动（命名实例）
The certificate chain was issued by an authority that is not trusted	加 TrustServerCertificate=True 或装证书
Timeout expired ... pool	连接池耗尽、长事务占满连接，检查 pooling 与事务释放

---

附录：一份可直接跑的建库建表到查询的完整示例

-- 1. 建库
CREATE DATABASE DemoDB COLLATE Chinese_PRC_CI_AS;
GO
USE DemoDB;
GO

-- 2. 建表
CREATE TABLE dbo.Device
(
    DeviceId   INT IDENTITY(1,1) NOT NULL,
    DeviceNo   VARCHAR(32)   NOT NULL,
    DeviceName NVARCHAR(100) NOT NULL,
    Status     TINYINT       NOT NULL CONSTRAINT DF_Device_Status DEFAULT (0),
    Qty        INT           NOT NULL CONSTRAINT DF_Device_Qty    DEFAULT (0),
    CreatedAt  DATETIME2(0)  NOT NULL CONSTRAINT DF_Device_Created DEFAULT (SYSDATETIME()),
    CONSTRAINT PK_Device PRIMARY KEY CLUSTERED (DeviceId),
    CONSTRAINT UQ_Device_No UNIQUE (DeviceNo),
    CONSTRAINT CK_Device_Status CHECK (Status BETWEEN 0 AND 3)
);
GO

-- 3. 建索引
CREATE NONCLUSTERED INDEX IX_Device_Status ON dbo.Device (Status) INCLUDE (DeviceNo, DeviceName);
GO

-- 4. 增
INSERT INTO dbo.Device (DeviceNo, DeviceName, Status, Qty)
VALUES ('D001', N'一号机', 1, 100),
       ('D002', N'二号机', 0, 0),
       ('D003', N'三号机', 2, 55);
GO

-- 5. 改
UPDATE dbo.Device SET Status = 2, Qty = 80 WHERE DeviceNo = 'D001';
GO

-- 6. 查
SELECT d.DeviceNo, d.DeviceName,
       CASE d.Status WHEN 0 THEN N'离线' WHEN 1 THEN N'运行' WHEN 2 THEN N'报警' ELSE N'维护' END AS 状态,
       d.Qty,
       ROW_NUMBER() OVER (ORDER BY d.Qty DESC) AS 产量排名
FROM dbo.Device d
WHERE d.Qty > 0
ORDER BY d.Qty DESC;
GO

-- 7. 删
DELETE FROM dbo.Device WHERE DeviceNo = 'D002';
GO

-- 8. 收尾
-- DROP DATABASE DemoDB;

转载自 CSDN-专业IT技术社区

原文链接：https://blog.csdn.net/lzcbaba/article/details/170362982