MS-SQL (SQL Server) 在企業級開發中積累了大量實用技巧，本文精選 12 個高頻實戰場景，涵蓋中文排序、密碼加密、元數據查詢、磁盤監控、表比較、進程管理、分頁查詢、重命名、系統表應用等核心能力，每個實例附帶標準代碼與原理解析，可直接應用於生產環境或作為面試/考核題庫。
SELECT * FROM TableName
ORDER BY CustomerName COLLATE Chinese_PRC_Stroke_CI_AS;原理： COLLATE 子句指定排序規則，Chinese_PRC_Stroke 為筆畫排序，CI_AS 為不區分大小寫、區分重音。同理可用 Chinese_PRC_BIN (二進制)、Chinese_PRC_CS_AS_KS_WS (區分大小寫/假名/全半角)。
-- 加密 (單向雜湊，不可逆)
SELECT PWDENCRYPT('MySecret123'); -- 返回 varbinary 雜湊值
-- 驗證 (對比明文與雜湊)
SELECT PWDCOMPARE('MySecret123', 0x0100...); -- 返回 1=匹配, 0=不匹配
-- 老版本 ENCRYPT (可逆，僅供舊系統兼容)
SELECT ENCRYPT('MySecret123');最佳實踐： 新系統禁用 ENCRYPT/PWDENCRYPT，改用 HASHBYTES('SHA2_256', @pwd + @salt) + 應用層 PBKDF2/Argon2/bcrypt。
DECLARE @cols VARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = COALESCE(@cols + ', ', '') + QUOTENAME(c.name)
FROM sys.columns c
JOIN sys.objects o ON c.object_id = o.object_id
WHERE o.name = 'TableA' AND o.type = 'U';
SET @sql = 'SELECT ' + @cols + ' FROM TableA';
EXEC sp_executesql @sql;用途： 動態構建視圖、ETL 映射、代碼生成器、避免 SELECT * 反模式。
EXEC master..xp_fixeddrives;返回： 驅動器字母、MB 可用空間。監控作業必備，可接入告警系統。
IF (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableA)
= (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableB)
PRINT '表數據完全一致';
ELSE
PRINT '表數據存在差異';原理： BINARY_CHECKSUM(*) 計算行級雜湊，CHECKSUM_AGG 聚合為表級指紋。極快 (僅掃描數據頁)，但極微概率碰撞，關鍵業務建議配合 EXCEPT/INTERSECT 或 tablediff 工具二次驗證。
DECLARE cur CURSOR GLOBAL FOR
SELECT 'KILL ' + RTRIM(spid)
FROM master.dbo.sysprocesses
WHERE program_name IN ('SQL Profiler', N'SQL 事件探查器');
OPEN cur;
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@cmd);
FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur; DEALLOCATE cur;現代替代： SQL Server 2008+ 使用 sys.dm_exec_sessions + KILL，或啟用擴展事件 (XEvents) 替代 Profiler。
-- 1. 開頭 N 條
SELECT TOP N * FROM TableName;
-- 2. 中間 M-N 條 (需主鍵 ID)
SELECT TOP (M-N) * FROM TableName
WHERE ID IN (SELECT TOP M ID FROM TableName ORDER BY ID)
ORDER BY ID DESC;
-- 3. 結尾 N 條
SELECT TOP N * FROM TableName ORDER BY ID DESC;生產建議： SQL 2012+ 直接用 OFFSET @skip ROWS FETCH NEXT @page ROWS ONLY，標準、可讀、優化器友好。
EXEC sp_renamedb 'Old_Name', 'New_Name';前提： 單用戶模式 (ALTER DATABASE Old_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE)，無活躍連接。
SELECT name FROM sysobjects WHERE xtype = 'U' AND status >= 0;現代寫法： SELECT name FROM sys.tables (sys.objects 視圖更規範)。
SELECT name FROM syscolumns WHERE id = OBJECT_ID('TableName');現代寫法： SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('TableName')。
SELECT DISTINCT o.name, o.type_desc
FROM sys.objects o
JOIN sys.sql_modules m ON o.object_id = m.object_id
WHERE m.definition LIKE '%TableName%';優於舊法： syscomments 限制 4000 字符，sys.sql_modules 支援 MAX 長度，不漏報長過程。
SELECT name AS ProcedureName FROM sysobjects WHERE xtype = 'P';現代寫法： SELECT name FROM sys.procedures 或 SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE'。
| 舊系統表 (兼容視圖) | 新目錄視圖 (推薦) | 優勢 |
|---|---|---|
| sysobjects | sys.objects / sys.tables / sys.procedures | 類型細分、元數據豐富、權限可見性 |
| syscolumns | sys.columns | 包含計算列、稀疏列、系統類型ID |
| syscomments | sys.sql_modules | 定義無長度限制、含執行設置 |
| sysindexes | sys.indexes / sys.partitions / sys.dm_db_index_physical_stats | 碎片率、頁數、鎖統計等運維指標 |
這 12 個實例覆蓋了 MS-SQL 開發中業務邏輯 (排序、加密、分頁)、元數據驅動 (動態列、依賴查找)、運維管理 (磁盤、進程、比較、重命名) 三大核心維度。現代開發請全面拥抱 sys.* 目錄視圖、標準 INFORMATION_SCHEMA、DMV/DMF 動態管理視圖，棄用 sysobjects/syscolumns/syscomments 等兼容視圖。將這些模式內化為組件庫 (如 dbo.fn_GetTableColumns, dbo.sp_CompareTables)，能顯著提升團隊開發效率與代碼質量。
