MS SQL是Microsoft SQL Server的简称，是由微软公司开发的一款关系型数据库管理系统（RDBMS,Relational DataBase Management System）。它支持在Windows和Linux上运行，广泛应用于企业级数据库市场，适用于大型企业网站和应用程序。MSSQL使用**Transact-SQL (T-SQL)**进行数据操作，提供了强大的数据管理和查询功能。

MS SQL Server 面试高频“数据库管理”知识清单
（按“概念 → 关键语法/命令 → 一句话场景示例”整理，背熟即可应对 90 % 提问）

---

一、体系结构 & 核心概念

概念	一句话解释	面试追问点
实例（Instance）	一套 sqlservr.exe + 独占内存/端口	默认实例 MSSQLSERVER vs 命名实例
系统数据库	master/model/msdb/tempdb/resource	master 坏了怎么办？重建步骤
页 Page（8 KB）/ 区 Extent（8 页）	I/O 最小单位	计算表占多少页 = row*rows/8060
事务日志 LDF	WAL 机制，先写日志再写数据	日志截断 / 收缩 / 备份模式
恢复模式	FULL / BULK_LOGGED / SIMPLE	不同模式对日志截断的影响

---

二、DDL（库、表、索引、约束）

类别	高频命令	一句话示例
创建/删除库	CREATE DATABASE / DROP DATABASE	CREATE DATABASE Sales ON (NAME='Sales_dat', FILENAME='D:\Data\Sales.mdf')
文件组 FILEGROUP	把大表分区到不同磁盘	ALTER DATABASE Sales ADD FILEGROUP FG2018
建表	CREATE TABLE … PK / FK / CHECK / UNIQUE	CREATE TABLE Orders(id INT PRIMARY KEY, customer_id INT REFERENCES Customers(id))
修改表	ALTER TABLE … ADD / ALTER COLUMN / DROP COLUMN	ALTER TABLE Orders ADD order_date DATETIME NOT NULL CONSTRAINT df_order_date DEFAULT GETDATE()
索引	CREATE [CLUSTERED / NONCLUSTERED] INDEX	CREATE NONCLUSTERED INDEX IX_OrderDate ON Orders(order_date)
覆盖索引	INCLUDE 列减少回表	CREATE INDEX IX_Cover ON Orders(order_date) INCLUDE(customer_id, amount)
视图	CREATE VIEW vSales AS … WITH SCHEMABINDING	防止基础对象被删除
分区表	CREATE PARTITION FUNCTION / SCHEME	按日期分区 > 快速切换归档

---

三、DML（增删改查 + 事务）

命令	必背细节	面试示例
INSERT	IDENTITY_INSERT ON 可显式插入自增列	SET IDENTITY_INSERT Orders ON; INSERT Orders(id) VALUES(1001);
UPDATE	加 OUTPUT 子句查看旧值	UPDATE Orders SET status='S' OUTPUT deleted.status AS old_status
DELETE vs TRUNCATE	日志量、触发器、重置自增列	TRUNCATE 不激活触发器、不能带 WHERE
MERGE	一条语句完成 UPSERT	MERGE Target USING Source ON … WHEN MATCHED THEN UPDATE … WHEN NOT MATCHED THEN INSERT …
事务 ACID	BEGIN TRAN / COMMIT / ROLLBACK / SAVEPOINT	死锁优先级：SET DEADLOCK_PRIORITY LOW

---

四、查询与性能

主题	关键词	一句话示例
连接	INNER / LEFT / RIGHT / FULL / CROSS APPLY	用 APPLY 代替游标逐行计算
子查询 vs JOIN	EXISTS > IN 性能	SELECT * FROM A WHERE EXISTS(SELECT 1 FROM B WHERE B.id=A.id)
窗口函数	ROW_NUMBER / RANK / DENSE_RANK / LAG / LEAD	SELECT ROW_NUMBER() OVER(PARTITION BY dept ORDER BY salary DESC) AS rn
CTE 递归	WITH … UNION ALL 自引用	查 BOM 或组织架构树
执行计划	SET STATISTICS IO / TIME ON；显示实际执行计划	关注 Table Scan → Seek、RID Lookup → Key Lookup
参数嗅探	OPTION (RECOMPILE)、OPTIMIZE FOR、局部变量转常量	解决同一查询不同参数速度差异大
统计信息	UPDATE STATISTICS WITH FULLSCAN	过期的统计信息会导致走错执行计划
索引碎片	sys.dm_db_index_physical_stats > 30 % 重建	ALTER INDEX ALL ON Orders REBUILD

---

五、备份 / 恢复 / 高可用

任务	命令 / 方案	面试话术
完整备份	BACKUP DATABASE Sales TO DISK=‘D:\Bak\Sales.bak’ WITH COMPRESSION	压缩备份节省 60 % 空间
差异备份	BACKUP DATABASE Sales TO DISK=‘…’ WITH DIFFERENTIAL	每天 1 次完整 + 每 15 min 差异
日志备份	BACKUP LOG Sales TO DISK=‘…’	保证 FULL 模式下点-in-time 恢复
还原	RESTORE DATABASE Sales FROM DISK=‘…’ WITH NORECOVERY → RESTORE LOG …	恢复到指定时间点：STOPAT = '2024-08-28 15:30:00'
CHECKDB	DBCC CHECKDB(‘Sales’) WITH NO_INFOMSGS	每周至少一次，发现页损坏
镜像 / AlwaysOn	同步/异步模式、自动故障转移	见证服务器作用、仲裁问题
日志传送	主→辅间隔分钟级，仅可读	成本低，无自动故障转移

---

六、安全与权限

对象	命令	示例
登录 Login vs 用户 User	登录实例级，用户数据库级	CREATE LOGIN tom WITH PASSWORD='P@ss' → CREATE USER tom FOR LOGIN tom
角色	server role / db role / 自定义	ALTER ROLE db_datareader ADD MEMBER tom
权限	GRANT / DENY / REVOKE	GRANT SELECT, INSERT ON Orders TO tom
行级安全	CREATE SECURITY POLICY … WITH (PREDICATE)	让销售只看自己区域订单
动态脱敏	ADD MASKED WITH (FUNCTION = 'partial(1,"XXX",0)')	面试问 GDPR 合规方案

---

七、监控 & 维护

DMV / 命令	作用	面试场景
sys.dm_exec_requests / sys.dm_exec_sessions	查看阻塞链	SELECT blocking_session_id, wait_type, wait_time
sys.dm_os_wait_stats	服务器级别瓶颈	常见 CXPACKET、PAGEIOLATCH_XX
sys.dm_db_missing_index_details	推荐索引	结合执行计划验证
Ola Hallengren 维护脚本	免费开源：备份/索引/统计信息	回答“如何做自动化维护”
SQL Server Agent Job	定时任务	每天 2:00 全备，每 15 min 日志备

---

八、常见面试题速答

1. 日志文件暴涨怎么排查？
   查 log_reuse_wait_desc → 如果是 LOG_BACKUP 先做日志备份，再收缩。
2. 索引重建还是重组？
   碎片 5–30 % 重组 REORGANIZE；>30 % 或深度 >3 层则重建 REBUILD。
3. 如何迁移 1 TB 库到新服务器最快？
   备份压缩 → 复制 bak → 还原；或 backup to url + azcopy；大库可先用日志传送。
4. 出现死锁怎么办？
   开启 trace 1222 捕获死锁图 → 优化索引/调整事务顺序，必要时 WITH (ROWLOCK, UPDLOCK) 提示。
5. tempdb 配置最佳实践？
   数据文件 = CPU 核数 ≤ 8，统一大小，开启 TF 1117/1118，放到最快的 SSD。

---

把上述清单按“概念→命令→场景”熟记，可在 MS SQL Server DBA / 开发面试中快速输出关键词，体现专业深度。