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 日志备 |
八、常见面试题速答
- 日志文件暴涨怎么排查？
查 log_reuse_wait_desc → 如果是 LOG_BACKUP 先做日志备份，再收缩。
- 索引重建还是重组？
碎片 5–30 % 重组 REORGANIZE；>30 % 或深度 >3 层则重建 REBUILD。
- 如何迁移 1 TB 库到新服务器最快？
备份压缩 → 复制 bak → 还原；或 backup to url + azcopy；大库可先用日志传送。
- 出现死锁怎么办？
开启 trace 1222 捕获死锁图 → 优化索引/调整事务顺序，必要时 WITH (ROWLOCK, UPDLOCK) 提示。
- tempdb 配置最佳实践？
数据文件 = CPU 核数 ≤ 8，统一大小，开启 TF 1117/1118，放到最快的 SSD。
把上述清单按“概念→命令→场景”熟记，可在 MS SQL Server DBA / 开发面试中快速输出关键词，体现专业深度。