MSSQL 特有功能

SQL Server Management Studio（SSMS）

SSMS 是管理 SQL Server 的圖形化工具，就像 Visual Studio 之於 C#，SSMS 之於 SQL Server。

常用功能

• 物件總管：瀏覽資料庫、資料表、索引等
• 查詢編輯器：撰寫和執行 SQL
• 執行計畫：圖形化顯示查詢效能
• 活動監視器：監控伺服器狀態

-- 在 SSMS 中常用的查詢
-- 查看所有資料庫
SELECT name FROM sys.databases;            -- 列出所有資料庫名稱

-- 查看某個資料庫的所有資料表
SELECT TABLE_NAME                          -- 選取資料表名稱
FROM INFORMATION_SCHEMA.TABLES             -- 從資訊架構表
WHERE TABLE_TYPE = 'BASE TABLE';           -- 只要基本資料表（排除 View）

-- 查看資料表的欄位資訊
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE -- 選取欄位名、資料型別、是否可為空
FROM INFORMATION_SCHEMA.COLUMNS            -- 從欄位資訊表
WHERE TABLE_NAME = 'Students';             -- 指定資料表名稱

Stored Procedures（預存程序）

預存程序是預先編譯並儲存在資料庫中的 SQL 程式。

想像一道菜的食譜：你把做菜步驟寫好存起來（CREATE PROCEDURE），之後只要說一聲「做這道菜」（EXEC），就會自動按步驟執行。

-- 建立預存程序：查詢學生成績報表
CREATE PROCEDURE sp_GetStudentReport       -- 建立名為 sp_GetStudentReport 的預存程序
    @MinScore INT = 60,                    -- 參數：最低分數（預設 60）
    @CourseName NVARCHAR(100) = NULL       -- 參數：課程名稱（可選）
AS
BEGIN
    SET NOCOUNT ON;                        -- 不回傳「受影響的列數」訊息

    SELECT                                 -- 查詢學生成績報表
        s.Name AS StudentName,             -- 學生姓名
        c.CourseName,                      -- 課程名稱
        e.Score,                           -- 分數
        CASE                               -- 用 CASE 判斷等級
            WHEN e.Score >= 90 THEN N'優秀' -- 90 分以上
            WHEN e.Score >= 80 THEN N'良好' -- 80~89 分
            WHEN e.Score >= 60 THEN N'及格' -- 60~79 分
            ELSE N'不及格'                  -- 60 分以下
        END AS Grade                       -- 等級欄位
    FROM Students s                        -- 從學生表
    JOIN Enrollments e ON s.Id = e.StudentId -- 連接選課表
    JOIN Courses c ON e.CourseId = c.Id       -- 連接課程表
    WHERE e.Score >= @MinScore             -- 篩選分數
        AND (@CourseName IS NULL OR c.CourseName = @CourseName) -- 可選的課程篩選
    ORDER BY e.Score DESC;                 -- 依分數降序排列
END;

-- 執行預存程序
EXEC sp_GetStudentReport;                  -- 使用預設參數
EXEC sp_GetStudentReport @MinScore = 80;   -- 只看 80 分以上
EXEC sp_GetStudentReport @MinScore = 70, @CourseName = N'C# 程式設計'; -- 指定兩個參數

// 在 Dapper 中呼叫預存程序
var results = await conn.QueryAsync<StudentReport>( // 用 Dapper 查詢
    "sp_GetStudentReport",               // 預存程序名稱
    new { MinScore = 80 },                 // 傳入參數
    commandType: CommandType.StoredProcedure // 指定為預存程序
);

// 在 EF Core 中呼叫預存程序
var results = await db.Students            // 使用 EF Core
    .FromSqlRaw("EXEC sp_GetStudentReport @p0, @p1", 80, "C#") // 呼叫預存程序
    .ToListAsync();                        // 取得結果

Views（檢視）

View 是預先定義好的查詢，用起來像一張虛擬的資料表。

-- 建立 View：學生成績摘要
CREATE VIEW vw_StudentSummary AS           -- 建立名為 vw_StudentSummary 的 View
SELECT                                     -- 查詢內容
    s.Id AS StudentId,                     -- 學生 ID
    s.Name AS StudentName,                 -- 學生姓名
    COUNT(e.Id) AS CourseCount,            -- 選課數量
    AVG(e.Score) AS AvgScore,              -- 平均分數
    MAX(e.Score) AS HighestScore           -- 最高分數
FROM Students s                            -- 從學生表
LEFT JOIN Enrollments e                    -- 左連接選課表
    ON s.Id = e.StudentId                  -- 連接條件
GROUP BY s.Id, s.Name;                     -- 依學生分組

-- 使用 View（就像查資料表一樣）
SELECT * FROM vw_StudentSummary            -- 查詢 View
WHERE AvgScore >= 80                       -- 篩選平均 80 分以上
ORDER BY AvgScore DESC;                    -- 依平均分數排序

Functions（使用者定義函數）

-- 純量函數（回傳單一值）
CREATE FUNCTION fn_GetLetterGrade          -- 建立計算等級的函數
(
    @Score INT                             -- 參數：分數
)
RETURNS NVARCHAR(10)                       -- 回傳型別：字串
AS
BEGIN
    RETURN CASE                            -- 判斷等級
        WHEN @Score >= 90 THEN N'A'        -- 90 分以上
        WHEN @Score >= 80 THEN N'B'        -- 80~89 分
        WHEN @Score >= 70 THEN N'C'        -- 70~79 分
        WHEN @Score >= 60 THEN N'D'        -- 60~69 分
        ELSE N'F'                          -- 60 分以下
    END;
END;

-- 使用函數
SELECT Name, Score,                        -- 選取姓名和分數
    dbo.fn_GetLetterGrade(Score) AS Grade   -- 呼叫函數計算等級
FROM Students;                             -- 從學生表查詢

Triggers（觸發器）

⚠️ 觸發器很強大但也很危險——它們會在特定操作時自動觸發，如果不小心可能造成意想不到的副作用。

-- 建立觸發器：記錄學生資料的修改歷史
CREATE TRIGGER trg_StudentAudit            -- 建立觸發器
ON Students                                -- 綁定在 Students 表上
AFTER UPDATE                               -- 在 UPDATE 之後觸發
AS
BEGIN
    SET NOCOUNT ON;                        -- 不回傳影響列數

    INSERT INTO StudentAuditLog            -- 寫入審計日誌表
    (
        StudentId,                         -- 學生 ID
        OldName,                           -- 修改前的姓名
        NewName,                           -- 修改後的姓名
        ChangedAt                          -- 修改時間
    )
    SELECT                                 -- 從 inserted 和 deleted 虛擬表取值
        i.Id,                              -- 學生 ID
        d.Name,                            -- deleted 表 = 修改前的值
        i.Name,                            -- inserted 表 = 修改後的值
        GETDATE()                          -- 目前時間
    FROM inserted i                        -- inserted = 新值
    JOIN deleted d ON i.Id = d.Id;         -- deleted = 舊值
END;

💡 為什麼要小心觸發器？

• 觸發器是「隱形」的——看程式碼看不到它會執行
• 可能造成連鎖觸發（觸發器 A 觸發觸發器 B）
• 偵錯困難，效能影響不容易發現
• 建議：優先使用應用程式邏輯或 EF Core 的 SaveChanges 事件

SQL Server Profiler 基礎

SQL Server Profiler 用來監控資料庫的即時活動。

常見用途

• 找出慢查詢（Slow Query）
• 監控哪些 SQL 被執行
• 偵錯效能問題

-- 替代方案：用 DMV（Dynamic Management Views）查詢效能資訊
-- 查看目前正在執行的查詢
SELECT                                     -- 查詢執行中的請求
    r.session_id,                          -- 連線 ID
    r.status,                              -- 狀態
    r.command,                             -- 命令類型
    t.text AS QueryText,                   -- SQL 文字
    r.total_elapsed_time / 1000 AS ElapsedSec -- 已執行秒數
FROM sys.dm_exec_requests r                -- 從執行請求 DMV
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t -- 取得 SQL 文字
WHERE r.session_id > 50;                   -- 排除系統連線

-- 查看最耗資源的查詢（Top 10）
SELECT TOP 10                              -- 取前 10 名
    qs.total_elapsed_time / qs.execution_count AS AvgTime, -- 平均執行時間
    qs.execution_count,                    -- 執行次數
    SUBSTRING(t.text, 1, 200) AS QueryText -- SQL 文字（前 200 字）
FROM sys.dm_exec_query_stats qs            -- 從查詢統計 DMV
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t -- 取得 SQL 文字
ORDER BY AvgTime DESC;                     -- 依平均時間降序

系統資料庫

-- 查看 tempdb 使用狀況
SELECT                                     -- 查詢 tempdb 空間使用
    SUM(unallocated_extent_page_count) * 8 / 1024 AS FreeSpaceMB,  -- 可用空間
    SUM(internal_object_reserved_page_count) * 8 / 1024 AS InternalMB, -- 內部物件
    SUM(user_object_reserved_page_count) * 8 / 1024 AS UserMB         -- 使用者物件
FROM sys.dm_db_file_space_usage;           -- 從空間使用 DMV 查詢

🤔 我這樣寫為什麼會錯？

❌ 錯誤 1：使用 Cursor（游標）代替集合操作

-- ❌ 用游標逐列處理（非常慢）
DECLARE @Id INT, @Score INT                -- 宣告變數
DECLARE student_cursor CURSOR FOR          -- 宣告游標
    SELECT Id, Score FROM Students         -- 游標查詢

OPEN student_cursor                        -- 開啟游標
FETCH NEXT FROM student_cursor INTO @Id, @Score -- 取得第一列

WHILE @@FETCH_STATUS = 0                   -- 迴圈處理每一列
BEGIN
    UPDATE Students SET Score = @Score + 5 -- 逐列更新
    WHERE Id = @Id                         -- 一次只更新一列
    FETCH NEXT FROM student_cursor INTO @Id, @Score -- 取下一列
END

CLOSE student_cursor                       -- 關閉游標
DEALLOCATE student_cursor                  -- 釋放游標
-- 逐列處理 → 超級慢！像是一個一個搬磚頭

-- ✅ 用集合操作一次搞定
UPDATE Students                            -- 一個 UPDATE 語句
SET Score = Score + 5;                     -- 一次更新所有列
-- 集合操作 → 超級快！像是開怪手一次搬一堆

❌ 錯誤 2：觸發器連鎖導致效能問題

-- ❌ 觸發器 A 修改表 B → 表 B 上的觸發器又修改表 C → ...
-- 造成連鎖反應，難以追蹤和偵錯
-- 而且可能導致無限迴圈！

-- ✅ 盡量用應用程式邏輯取代觸發器
-- 在 EF Core 的 SaveChanges 中處理審計邏輯

❌ 錯誤 3：直接修改系統資料庫

-- ❌ 絕對不要手動修改 master、tempdb 的資料
-- USE master
-- DELETE FROM sys.objects ... ← 不要這樣做！

-- ✅ 只使用官方提供的系統預存程序和 DDL 語句
-- 例如用 ALTER DATABASE 修改設定
-- 用 sp_helpdb 查看資料庫資訊
EXEC sp_helpdb;                            -- 查看所有資料庫的詳細資訊

💡 重點整理