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 / 開發面試中快速輸出關鍵詞，體現專業深度。