MS-SQL 開發常用技巧大全：排序、加密、分頁、對比、元數據查詢

前言

SQL Server (T-SQL) 開發中，高頻需求往往有「一行搞定」的系統函數或系統表查詢。本文匯總 14 類實用技巧，含代碼可直接落地。

一、按中文姓氏筆畫排序

SELECT * FROM Customer
ORDER BY CustomerName COLLATE Chinese_PRC_Stroke_CI_AS;

二、密碼加密與驗證

-- 加密（僅演示，生產請用 HASHBYTES + SALT）
SELECT PWDENCRYPT('plain_pwd');          -- 返回 varbinary
SELECT PWDCOMPARE('plain_pwd', 0xHash);  -- 1=匹配, 0=不匹配

現代最佳實踐： HASHBYTES('SHA2_256', CONCAT(salt, pwd)) + 獨立 salt 欄位。

三、動態獲取表所有欄位並查詢

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(name), ',')  -- SQL 2017+
FROM sys.columns WHERE object_id = OBJECT_ID('TableA');
SET @sql = 'SELECT ' + @cols + ' FROM TableA';
EXEC sp_executesql @sql;

四、查看磁盤剩餘空間

EXEC master..xp_fixeddrives;

五、快速比較兩表數據是否完全相同

IF (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableA)
   = (SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM TableB)
    PRINT '相等';
ELSE PRINT '不相等';

六、殺掉所有 SQL Profiler / 事件探查器 進程

DECLARE cur CURSOR FOR
SELECT 'KILL ' + CAST(spid AS VARCHAR)
FROM master.dbo.sysprocesses
WHERE program_name IN ('SQL Profiler', 'SQL 事件探查器');
OPEN cur;
DECLARE @cmd VARCHAR(100);
FETCH NEXT FROM cur INTO @cmd;
WHILE @@FETCH_STATUS = 0 BEGIN
    EXEC(@cmd);
    FETCH NEXT FROM cur INTO @cmd;
END
CLOSE cur; DEALLOCATE cur;

七、分頁查詢三大模式（需主鍵 ID）

-- 1. 前 N 條
SELECT TOP N * FROM T ORDER BY ID;

-- 2. N 到 M 條（巢狀 TOP）
SELECT TOP (M-N+1) * FROM T
WHERE ID IN (SELECT TOP M ID FROM T ORDER BY ID)
ORDER BY ID DESC;

-- 3. 最後 N 條
SELECT TOP N * FROM T ORDER BY ID DESC;

現代寫法 (2012+)： ORDER BY ID OFFSET @offset ROWS FETCH NEXT @pageSize ROWS ONLY

八、重命名資料庫

EXEC sp_renamedb 'old_name', 'new_name';

九、獲取所有用戶表

SELECT name FROM sys.objects WHERE type = 'U' AND is_ms_shipped = 0;

十、獲取指定表所有欄位

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%';

十二、列出所有存儲過程

SELECT name FROM sys.objects WHERE type = 'P' AND is_ms_shipped = 0;

十三、查詢非 sa 建立的資料庫

SELECT dbid, name FROM master..sysdatabases WHERE sid <> 0x01;

十四、查詢表欄位與數據類型

SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'TableName';

總結

善用系統目錄視圖（sys.*、information_schema）與內建函數（CHECKSUM_AGG、STRING_AGG、COLLATE），能將大量運維開發工作化繁為簡。建議收藏備查，並逐步遷移至現代語法（OFFSET-FETCH、STRING_AGG、TRY_CAST 等）。