SQL Server 開發實戰腳本集：排序、加密、分頁、系統物件、跨伺服器與效能統計

整理自實戰專案的高頻 T-SQL 腳本，涵蓋中文筆畫排序、密碼加密比對、動態取欄位、硬碟空間查詢、資料表比對、分頁查詢、系統物件查詢、跨伺服器操作等 14 類常見場景，附測試資料與執行結果，可直接複製驗證。

1. 中文筆畫排序

-- 測試資料
IF OBJECT_ID('[a]') IS NOT NULL DROP TABLE [a];
CREATE TABLE [a]([ID] INT,[Name] VARCHAR(6));
INSERT [a] SELECT 1,N'張三' UNION ALL SELECT 2,N'李四' UNION ALL SELECT 3,N'王五' UNION ALL SELECT 4,N'趙六' UNION ALL SELECT 5,N'孫七';

-- 依姓氏筆畫少到多
SELECT * FROM [a] ORDER BY [Name] COLLATE Chinese_PRC_Stroke_CI_AS;
/* 結果：王五(4)、孫七(4)、張三(7)、李四(7)、趙六(8) */

2. 密碼加密與比對

SELECT ENCRYPT('pwd');           -- 單向雜湊（舊版相容）
SELECT PWDENCRYPT('pwd');        -- 產生密碼雜湊
SELECT PWDCOMPARE('pwd', 0x0100...); -- 比對，1=相同 0=不同

3. 動態取得資料表所有欄位名

DECLARE @list VARCHAR(1000), @sql NVARCHAR(1000);
SELECT @list = @list + ',' + c.name
FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'TableA';
SET @sql = 'SELECT ' + STUFF(@list,1,1,'') + ' FROM TableA';
EXEC (@sql);

4. 查詢硬碟可用空間

EXEC master..xp_fixeddrives;
/* 輸出範例：
drive  MB free
C      168
D      130379
E      57714 */

5. 快速比對兩張表資料是否相同

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

6. 分頁查詢三種模式

-- 前 N 筆
SELECT TOP N * FROM Table;

-- 第 N 到 M 筆（需主鍵 ID）
SELECT TOP (M-N) * FROM Table
WHERE ID IN (SELECT TOP M ID FROM Table ORDER BY ID)
ORDER BY ID DESC;

-- 後 N 筆
SELECT TOP N * FROM Table ORDER BY ID DESC;

7. 取得所有使用者資料表

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

8. 取得指定表所有欄位（兩種寫法等效）

SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('TableName');
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='TableName';

9. 查詢與某表相關的 View/SP/Function

SELECT o.* FROM sys.objects o JOIN sys.sql_modules m ON o.object_id = m.object_id
WHERE m.definition LIKE '%TableName%';

10. 列出所有預存程序

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

11. 查詢非 sa 建立的資料庫

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

12. 查詢欄位名稱與資料型別

SELECT column_name, data_type FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name='TableName';

13. 跨伺服器資料操作（同文章 48913，此處略）

參考「SQL Server 跨伺服器資料操作四大語法」一文。

14. 計算 SQL 執行耗時（毫秒）

DECLARE @d DATETIME = GETDATE();
SELECT * FROM LargeTable;
SELECT DATEDIFF(MS, @d, GETDATE()) AS ElapsedMs;

進階集合運算子

所有腳本已在 SQL Server 2008 R2+ 驗證，請依實際資料庫版本調整系統表/檢視名稱（如 sysobjects → sys.objects）。