SQL Server 進階操作指南：索引策略、核心 DML、聚合函數、字串/日期處理與郵件發送

一、索引策略與建立

叢集 vs 非叢集

建議

• 幾乎每張表都應有叢集索引 (通常主鍵)，避免 Heap 表碎片化。
• 非叢集索引覆蓋常用查詢欄位 (`INCLUDE` 非鍵欄位避免 Key Lookup)。
• 複合索引欄位順序：高選擇性、等值篩選欄位在前。
• 唯一索引 (`UNIQUE`) 兼具資料完整性與查詢最佳化提示。

建立語法

-- 非叢集
CREATE INDEX idx_orders_cust_date ON Orders (CustomerID, OrderDate);

-- 叢集 (若表無叢集)
CREATE CLUSTERED INDEX PK_Orders ON Orders (OrderID);

-- 唯一非叢集
CREATE UNIQUE NONCLUSTERED INDEX UQ_Users_Email ON Users (Email);

-- 含非鍵欄位 (覆蓋索引)
CREATE INDEX idx_Orders_Cover ON Orders (CustomerID) INCLUDE (OrderDate, TotalAmount);

管理工具

SSMS「索引/鍵」對話框、維護計畫重建/重組 (`ALTER INDEX ... REBUILD/REORGANIZE`)、`sys.dm_db_index_physical_stats` 監控碎片。

二、核心 DML 語句

INSERT

-- 單列
INSERT INTO Users (Name, Email) VALUES ('John', 'john@example.com');

-- 多列
INSERT INTO Users (Name, Email) VALUES 
('Alice', 'alice@example.com'), ('Bob', 'bob@example.com');

-- INSERT ... SELECT (大量轉存)
INSERT INTO Archive_Orders SELECT * FROM Orders WHERE OrderDate < '2023-01-01';

-- SELECT INTO (建新表複製)
SELECT * INTO Orders_Backup FROM Orders;

身分識別欄位：`SCOPE_IDENTITY()` 取得本會話/範圍內最新產生值 (優於 `@@IDENTITY`)。

DELETE

DELETE FROM Users WHERE LastLogin < DATEADD(year, -1, GETDATE());
-- 清空表 (最小記錄、重設身分識別、不觸發 Trigger)
TRUNCATE TABLE Staging_Data;

UPDATE

UPDATE Products SET Price = Price * 1.1 WHERE CategoryID = 5;
-- 多欄位
UPDATE Users SET LastLogin = GETDATE(), LoginCount = LoginCount + 1 WHERE UserID = 100;

三、聚合函數

別名：`SELECT AVG(Score) AS AvgScore FROM Exams` 供前端繫結。

四、字串與日期進階處理

模式比對

WHERE Name LIKE 'A%'           -- A 開頭
WHERE Name LIKE '%Smith'       -- Smith 結尾
WHERE Name LIKE '%John%'       -- 含 John
WHERE Code LIKE '[A-C]%'       -- A/B/C 開頭
WHERE Code LIKE '[^0-9]%'      -- 非數字開頭
WHERE Name LIKE '_a%'          -- 第二字為 a

SOUNDEX / DIFFERENCE

WHERE DIFFERENCE(LastName, 'Smith') >= 3  -- 發音相似
SELECT LastName, SOUNDEX(LastName) FROM Employees

去除空白

LTRIM(RTRIM(Col))  -- 兩端
TRIM(Col)          -- SQL 2017+ 兩端

日期格式化 (CONVERT 風格代碼)

SELECT CONVERT(VARCHAR(10), GETDATE(), 112) AS TodayISO

日期部分擷取

DATEPART(year,  OrderDate)   -- 年
DATEPART(month, OrderDate)   -- 月
DATEPART(day,   OrderDate)   -- 日
DATEPART(weekday, OrderDate) -- 週幾
DATENAME(month, OrderDate)   -- 月份名稱

五、Database Mail 發送郵件

-- 設定檔案/帳號 (一次)
EXEC msdb.dbo.sysmail_add_account_sp @account_name='AppMail', @email_address='app@corp.com', @mailserver_name='smtp.corp.com';
EXEC msdb.dbo.sysmail_add_profile_sp @profile_name='AppProfile';
EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name='AppProfile', @account_name='AppMail', @sequence_number=1;

-- 發送
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'AppProfile',
    @recipients = 'user@corp.com',
    @subject = '每日報表',
    @body = '請查收附件',
    @query = 'SELECT TOP 10 * FROM DailySales ORDER BY SaleDate DESC',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'DailySales.csv',
    @query_result_separator = ',',
    @query_result_header = 1;