字串排序與比較
-- 中文筆畫排序 (少到多)
ORDER BY Name COLLATE Chinese_PRC_Stroke_CI_AS;
-- 強制使用索引
SELECT id FROM TableName WITH (INDEX(IndexName)) WHERE num = @num;
-- 全文檢索替代 LIKE '%abc%'
SELECT id FROM t WHERE CHARINDEX('abc', col_name) > 0;
物件中繼資料查詢
-- 檢視物件定義
EXEC sp_helptext 'ObjectName';
-- 判斷資料表存在
IF EXISTS (SELECT 1 FROM sys.tables WHERE name = 'TableName') ...
-- 判斷暫存表存在
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL ...
-- 列出所有使用者表
SELECT name FROM sys.tables WHERE type = 'U';
-- 列出表所有欄位
SELECT name FROM sys.columns WHERE object_id = OBJECT_ID('TableName');
-- 列出所有檢視表
SELECT * FROM INFORMATION_SCHEMA.VIEWS;
-- 列出所有預存程序
SELECT name FROM sys.procedures;
常用函數速查
| 分類 | 函數 | 範例/說明 |
| 空值處理 | NULLIF(a,b) | a=b 則 NULL,否則 a |
| ISNULL(a,b) | a 不為 NULL 則 a,否則 b |
| COALESCE(a,b,c...) | 回傳第一個非 NULL |
| IIF(cond, t, f) | 2012+ 簡潔三元運算 |
| 數值捨入 | CEILING(x) | 大於等於 x 的最小整數 |
| FLOOR(x) | 小於等於 x 的最大整數 |
| ROUND(x, d) | 四捨五入到 d 位小數 |
| ROUND(x, d, 1) | 截斷到 d 位小數 |
| 字串操作 | STUFF(str, start, len, repl) | 位置替換:STUFF('ABCDEFG',2,1,'b')='AbCDEFG' |
| REPLACE(str, old, new) | 全域替換:REPLACE('ABC-BC','BC','*')='A*-*' |
| REPLICATE(str, n) | 重複 n 次:REPLICATE('K',5)='KKKKK' |
| 字串搜尋 | CHARINDEX(sub, str [,start]) | 回傳位置(1-based),找不到=0 |
| PATINDEX(pattern, str) | 支援萬用字元:PATINDEX('%[0-9]%', str) |
| LIKE | 標準模式比對 |
日期時間運算精選
-- 當前日期加 1 月
SELECT DATEADD(MONTH, 1, GETDATE());
-- 本月第一天 00:00:00
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);
-- 本月最後一天 23:59:59.997
SELECT DATEADD(MS, -3, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+1, 0));
-- 本季第一天
SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0);
-- 今年最後一天
SELECT DATEADD(MS, -3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE())+1, 0));
-- 判斷閏年
SELECT CASE WHEN DAY(DATEADD(DAY, -1, DATEADD(MONTH, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)))) = 29 THEN '閏年' ELSE '平年' END;
延遲/定時執行
PRINT '開始'; WAITFOR DELAY '00:00:05'; PRINT '5秒後';
PRINT '等到12點'; WAITFOR TIME '12:00:00'; PRINT '12點執行';
遠端資料存取 (OPENROWSET/OPENDATASOURCE)
-- 單次查詢
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=RemoteIP;UID=sa;PWD=pwd;', 'SELECT * FROM DB.dbo.Table');
-- 信任連線
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=RemoteIP;Trusted_Connection=yes;', DB.dbo.Table);
-- 寫入遠端
INSERT OPENROWSET('SQLNCLI', 'Server=RemoteIP;Trusted_Connection=yes;', 'SELECT Col FROM DB.dbo.Table')
SELECT Col FROM LocalTable;
分頁查詢 (SQL 2012+ OFFSET/FETCH)
SELECT * FROM TableName
ORDER BY Id
OFFSET (@PageIndex-1)*@PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;