SQL Server 日常開發運維中，高頻查詢語句集中在元數據探索、時間計算、業務模式匹配、鎖/約束診斷 四大領域。本文將這些散落在文檔、博客、腳本庫中的碎片知識，重構為結構化、可直接複製執行、附帶參數說明的查詢手冊，覆蓋 90% 日常場景。
一、元數據探索：認清數據庫結構
1. 數據庫屬性快照
EXEC sp_helpdb 'DatabaseName';
-- 或
SELECT DATABASEPROPERTYEX('Northwind', 'IsAutoShrink') AS IsAutoShrink,
DATABASEPROPERTYEX('Northwind', 'Recovery') AS RecoveryModel,
DATABASEPROPERTYEX('Northwind', 'Collation') AS Collation;
2. 列出所有用戶表
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';
-- 等價於
SELECT name FROM sys.tables;
3. 獲取指定表所有列信息
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE, COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName';
4. 查找包含特定列名的表
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%CustomerID%';
二、時間計算：掌握 DATEPART/DATENAME/DATEDIFF/DATEADD
核心函數速查
| 函數 | 用途 | 返回類型 |
|---|
GETDATE() | 當前系統日期時間 (datetime) | datetime |
SYSDATETIME() | 高精度當前時間 (datetime2(7)) | datetime2 |
DATEPART(datepart, date) | 提取日期部分 (整數) | int |
DATENAME(datepart, date) | 提取日期部分 (字串) | nvarchar |
DATEDIFF(datepart, start, end) | 兩日期差值 (整數) | int |
DATEADD(datepart, number, date) | 日期加減運算 | 同 date 類型 |
datepart 縮寫對照表
| 日期部分 | 縮寫 |
|---|
| Year | yy, yyyy |
| Quarter | qq, q |
| Month | mm, m |
| DayOfYear | dy, y |
| Day | dd, d |
| Week | wk, ww |
| Weekday | dw |
| Hour | hh |
| Minute | mi, n |
| Second | ss, s |
| Millisecond | ms |
常用時間格式化輸出 (CONVERT style)
SELECT CONVERT(varchar(30), GETDATE(), 102) AS ANSI_Style; -- 2023.01.01
SELECT CONVERT(varchar(30), GETDATE(), 113) AS Euro_Style; -- 01 01 2023 13:30:45:123
SELECT CONVERT(varchar(30), GETDATE(), 111) AS Japan_Style; -- 2023/01/01
SELECT CONVERT(varchar(30), GETDATE(), 120) AS ODBC_Style; -- 2023-01-01 13:30:45
SELECT CONVERT(varchar(30), GETDATE(), 121) AS ODBC_MS_Style; -- 2023-01-01 13:30:45.123
實用時間計算模式
-- 本月第一天 00:00:00
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0);
-- 本月最後一天 23:59:59.997
SELECT DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0));
-- 當前週一 (週日為週首，受 SET DATEFIRST 影響)
SELECT DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0);
-- 年齡計算 (精確到天)
SELECT DATEDIFF(day, BirthDate, GETDATE()) / 365.25;
三、業務查詢模式：LIKE/IN/BETWEEN/IS NULL 綜合應用
| 場景 | 語句模板 |
|---|
| 第二字為 'u' 且長度 3 | WHERE Name LIKE '_u_' |
| 以 'S' 開頭 | WHERE Name LIKE 'S%' |
| 以 'S'、'D' 或 'J' 開頭 | WHERE Name LIKE '[SDJ]%' |
| 不以 'S'、'D'、'J' 開頭 | WHERE Name LIKE '[^SDJ]%' |
| 查詢 NULL 值 (如無成績) | WHERE Grade IS NULL |
| 按年齡升序/降序 | ORDER BY Age [DESC] |
| 範圍查詢 (50-100 含邊界) | WHERE Amount BETWEEN 50 AND 100 |
| 集合包含 (IN 列表) | WHERE Status IN (1, 3, 5) |
四、運維診斷：鎖、約束、對象類型
1. 即時鎖信息
EXEC sp_lock;
-- 現代替代 (含會話、語句、等待資源)
SELECT * FROM sys.dm_tran_locks;
2. 表級約束信息
EXEC sp_helpconstraint 'TableName';
-- 查看特定約束
EXEC sp_help 'ConstraintName';
3. 按類型查找對象
-- 用戶表 (U)
SELECT name FROM sys.objects WHERE type = 'U';
-- 視圖 (V)
SELECT name FROM sys.objects WHERE type = 'V';
-- 存儲過程 (P)
SELECT name FROM sys.objects WHERE type = 'P';
-- 函數 (FN, IF, TF)
SELECT name FROM sys.objects WHERE type IN ('FN', 'IF', 'TF');
五、進階診斷查詢 (DMV 專區，SQL 2005+)
1. 高消耗查詢 (CPU/IO/時間)
SELECT TOP 10
qs.total_worker_time / qs.execution_count AS Avg_CPU_Time,
qs.total_logical_reads / qs.execution_count AS Avg_Logical_Reads,
qs.execution_count,
SUBSTRING(qt.text, qs.statement_start_offset/2 + 1,
(CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS Query_Text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY Avg_CPU_Time DESC;
2. 缺失索引建議
SELECT
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS Improvement_Measure,
mid.statement AS Table_Name,
mid.equality_columns, mid.inequality_columns, mid.included_columns
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY Improvement_Measure DESC;
3. 索引碎片率與使用統計
SELECT
OBJECT_NAME(ips.object_id) AS TableName,
i.name AS IndexName,
ips.index_type_desc,
ips.avg_fragmentation_in_percent,
ips.page_count,
ius.user_seeks, ius.user_scans, ius.user_lookups
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
LEFT JOIN sys.dm_db_index_usage_stats ius ON ips.object_id = ius.object_id AND ips.index_id = ius.index_id AND ius.database_id = DB_ID()
WHERE ips.page_count > 100
ORDER BY ips.avg_fragmentation_in_percent DESC;
總結
本手冊將 MS-SQL 高頻查詢歸類為元數據 (INFORMATION_SCHEMA/sys.*)、時間 (DATEPART/DATENAME/CONVERT)、業務模式 (LIKE/IN/BETWEEN/NULL)、運維診斷 (sp_lock/sp_helpconstraint/DMV) 四大體系。建議將常用查詢封裝為 dbo.sp_* 存儲過程或 dbo.fn_* 函數 (如 dbo.fn_GetTableColumns, dbo.sp_FindBlocking)，納入團隊共享腳本庫，實現標準化、可複用、可測試 的查詢治理。記住：元數據查 INFORMATION_SCHEMA 兼容、運維查 DMV 深度、時間算用 DATEADD/DATEDIFF 避開邊界陷阱。
