## [.NET 筆記 006] MS SQL Server 資料庫設計

###### 📅 2026-04-13

### Step-1. 下載 MS SQL Server
1. 搜尋 "sql server download"

2. 找到微軟下載頁: https://www.microsoft.com/zh-tw/sql-server/sql-server-downloads

3. 下載 SQL Server 2025 開發人員版本 > Standard (標準版)

4. 安裝方式: Custom (自訂)

5. 語言: Chinese (Traditionaal) (繁體中文)

6. 安裝路徑: "C:\SQL2025"

7. 點擊: Install，等待 3 分鐘左右安裝

8. SQL Server 安裝中心

## Step-2. 安裝 MS SQL Server
1. 點擊: 左側 "安裝" > 右側 "新的 SQL Server 獨立安装或將功能新增至現有安裝"

2. 指定免費版本: 選 "Standard Developer" > 接受授權條款

3. 勾選: "使用 Microsoft Update 檢查更新"

4. 取消勾選: "適用於 SQL Server的 Azure 延伸模組"

5. 特徵選取 > 功能: 只勾選 "資料庫引擎服務" (路徑: C:/ProgramFiles/Microsoft SQL Server)

6. 執行個體組態 > 選 "具名個體" > 填入自訂名稱: "SQL2025"
(執行個體識別碼: SQL2025)
(SQL Server 目錄: C:\Program Files\Microsoft SQL Server\MSSQL17.SQL2025)

7. 伺服器組態 > (預設)
(SQL Server Agent: NT Service\SQLAgent\$SQL2025)
(SQL Server Database Engine: NT Service\MSSQL$SQL2025)
(SQL Server Browser: NT AUTHORITY\LOCAL SERVICE)

8. 資料庫的引擎組態 > 驗證
(a) 選 "混合模式"
(b) 指定 SQL Server 管理員 > 加入目前使用者
\(c) 輸入密碼 & 確認密碼: 填入 "Windows 登入密碼"

9. 準備安裝 > (預設)
(C:\Program Files\Microsoft SQL Server\170\Setup Bootstrap\Log\20260413_114235\ConfigurationFile.ini)

10. 點擊: 安裝

## Step-3. 安裝 SQL Server 管理工具 (SSMS, SQL Server Management Studio)
1. 點擊: 左側 "安裝" > 右側 "安裝 SQL Server 管理工具"
2. 自動以瀏覽器開啟網頁：https://learn.microsoft.com/zh-tw/ssms/install/install
3. 點擊: "Download SQL Server Management Studio 22 安裝器"
4. 下載並點擊 vs_SSMS.exe 後，若先前已安裝 Visual Studio 會跳轉到 "工作負載" > 點擊: 安裝
(版本: 22.4.1)
(發行編號：22.4.1, 上映日期：2026年3月18日)
(連結: https://learn.microsoft.com/zh-tw/ssms/release-notes-22#22.4.1)

## Step-4. SSMS 基本操作
1. Windows 工具列 > 搜尋 > 輸入: "SSMS"，點擊: "SQL Server Management Studio 2022"

2. 回顧先前設定: 在先前的【Step 2. (6)】，已經將【執行個體組態】，設定為 "SQL2025"

3. 【連線】設定
* 伺服器名稱: ".\SQL2025"（必須在執行個體名稱前方加上 ".\"）
(PS: 點 . 代表本機；後面一定要加上反斜線 \ 銜接執行個體名稱)
* 驗證: SQL Server 驗證
* 帳號: sa
(PS: sa 代表 System Administrator (系統管理員)，內建的資料庫最高權限帳號)
* 密碼: (填入 Windows 登入密碼)
* 加密: 設為 "選用"，並勾選 "信任伺服器憑證"

4. 點擊: "連接"

## Step-5. 建立資料前準備

### 5-1. 資料庫設計 - ERD (實體關聯圖)

---

### 5-2. (Optional) 如果要用設計工具(UI)建立資料的話
至：工具 > 選項 > 設計師 > 資料表... > 取消勾選: "防止儲存..."

---

### 5-3. 執行順序總覽

| 檔案 | 說明 |
|------|------|
| `1_create_database.sql` | 刪除舊資料庫（若存在）並重建 |
| `2_create_table_roles.sql` | 建立 Roles 資料表 |
| `3_insert_table_roles.sql` | 插入 Roles 範例資料 |
| `4_create_table_users.sql` | 建立 Users 資料表 |
| `5_insert_table_users.sql` | 插入 Users 範例資料 |
| `6_create_table_devices.sql` | 建立 Devices 資料表 |
| `7_insert_table_devices.sql` | 插入 Devices 範例資料 |
| `8_create_table_roledevices.sql` | 建立 RoleDevices 資料表 |
| `9_insert_table_roledevices.sql` | 插入 RoleDevices 範例資料 |
| `10_set_all_foreign_keys.sql` | 建立所有外鍵約束 (FK) |

> ⚠️ **注意**：FK 必須最後設定，確保所有資料表與資料都已建立完成

---

### 5-4. ⚠️ 不可不知 ⚠️

Q: 建完資料表也按了重新整理 但打資料表名稱會有紅色波浪底線 為何
A:
(1) 解法
快捷鍵 "Ctrl + Shift + R"
或
編輯 (Edit) -> IntelliSense -> 重新整理本地快取 (Refresh Local Cache)

(2) 原因
因為 SQL Server Management Studio (SSMS) 的 IntelliSense（自動完成功能）快取還沒更新

即便你重新整理了左側的「物件總管」，SSMS 編輯視窗內部的語法檢查器（IntelliSense）可能還停留在舊的資料庫架構紀錄

---

## Step-6. 建立資料庫、建立資料表、插入資料

### 6-1. 刪除舊資料庫並重建

➡️ 開啟 SSMS，新增查詢，執行 `1_create_database.sql`

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'DeviceMonitor')
BEGIN
    ALTER DATABASE DeviceMonitor SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE DeviceMonitor;
END
GO

CREATE DATABASE DeviceMonitor;
GO

USE DeviceMonitor;
GO

➡️ 執行後，左側物件總管 > 右鍵 **Databases** > **重新整理**，確認 `DeviceMonitor` 已出現

---

### 6-2. 建立 Roles 資料表

> 💡 **為何先建 Roles？** Users 的 `RoleId` 會參考 Roles，所以 Roles 必須先存在

➡️ 執行 `2_create_table_roles.sql`

USE DeviceMonitor;
GO

CREATE TABLE Roles (
    Id          INT IDENTITY(1,1) NOT NULL,
    [Name]      NVARCHAR(20)      NULL,
    CreatedDate DATETIME          NULL,
    UpdatedDate DATETIME          NULL,

    CONSTRAINT PK_Roles PRIMARY KEY (Id)
);
GO

---

### 6-3. 插入 Roles 範例資料

➡️ 執行 `3_insert_table_roles.sql`

USE DeviceMonitor;
GO

INSERT INTO Roles ([Name], CreatedDate, UpdatedDate)
VALUES 
(N'系統管理者', GETDATE(), GETDATE()),
(N'北區使用者', GETDATE(), GETDATE()),
(N'南區使用者', GETDATE(), GETDATE());
GO

➡️ 執行後可用以下語句確認結果：

SELECT * FROM DeviceMonitor.dbo.Roles;

| Id | Name | CreatedDate | UpdatedDate |
|----|------|-------------|-------------|
| 1 | 系統管理者 | ... | ... |
| 2 | 北區使用者 | ... | ... |
| 3 | 南區使用者 | ... | ... |

---

### 6-4. 建立 Users 資料表

➡️ 執行 `4_create_table_users.sql`

USE DeviceMonitor;
GO

CREATE TABLE Users (
    Id          INT IDENTITY(1,1) NOT NULL,
    Account     VARCHAR(50)       NULL,
    [Name]      NVARCHAR(50)      NULL,
    Email       VARCHAR(50)       NULL,
    Pwd         VARCHAR(200)      NULL,
    IsActive    BIT               NULL,
    RoleId      INT               NULL,
    CreatedDate DATETIME          NULL,
    UpdatedDate DATETIME          NULL,

    CONSTRAINT PK_Users PRIMARY KEY (Id)
);
GO

---

### 6-5. 插入 Users 範例資料

➡️ 執行 `5_insert_table_users.sql`

USE DeviceMonitor;
GO

INSERT INTO Users (Account, [Name], Email, Pwd, IsActive, RoleId, CreatedDate, UpdatedDate)
VALUES 
('admin',        N'系統管理員', '[e-mail]',   'hash_pwd_123456', 1, 1, GETDATE(), GETDATE()),
('jeff_chiang',  N'江小魚',     '[e-mail]',        'hash_pwd_789012', 1, 2, GETDATE(), GETDATE()),
('mary_lee',     N'李瑪莉',     '[e-mail]',         'hash_pwd_345678', 1, 2, GETDATE(), GETDATE()),
('guest_01',     N'訪客帳號',   '[e-mail]',            'hash_pwd_abcdef', 0, 3, '2023-12-01 10:00:00', '2023-12-05 14:30:00'),
('tech_support', N'技術工程師', '[e-mail]', 'hash_pwd_qwerty', 1, 2, GETDATE(), GETDATE());
GO

➡️ (Optional) 確認資料：`SELECT * FROM DeviceMonitor.dbo.Users;`

---

### 6-6. 建立 Devices 資料表

➡️ 執行 `6_create_table_devices.sql`

USE DeviceMonitor;
GO

CREATE TABLE Devices (
    Id          INT IDENTITY(1,1) NOT NULL,
    Code        VARCHAR(50)       NULL,
    [Name]      NVARCHAR(50)      NULL,
    DeviceType  VARCHAR(20)       NULL,
    Params      NVARCHAR(200)     NULL,
    Lat         DECIMAL(9,6)      NULL,
    Lng         DECIMAL(9,6)      NULL,
    IsActive    BIT               NULL,
    Status      VARCHAR(20)       NULL,
    CreatedDate DATETIME          NULL,
    UpdatedDate DATETIME          NULL,

    CONSTRAINT PK_Devices PRIMARY KEY (Id)
);
GO

---

### 6-7. 插入 Devices 範例資料

➡️ 執行 `7_insert_table_devices.sql`

USE DeviceMonitor;
GO

INSERT INTO Devices (Code, [Name], DeviceType, Params, Lat, Lng, IsActive, Status, CreatedDate, UpdatedDate)
VALUES
('CCTV-N001', N'國道1號北上001', 'camera', N'{"VideoStream":"rtsp://192.168.1.1/stream1"}', 25.066390, 121.623800, 1, 'active', '2025-02-01 00:00:00', '2025-02-01 00:00:00'),
('CCTV-N002', N'國道1號北上002', 'camera', N'{"VideoStream":"rtsp://192.168.1.2/stream1"}', 24.763533, 120.999000, 1, 'active', '2025-02-01 00:00:00', '2025-02-01 00:00:00'),
('CCTV-N003', N'國道3號北上001', 'camera', N'{"VideoStream":"rtsp://192.168.1.3/stream1"}', 25.006725, 121.574200, 1, 'active', '2025-02-01 00:00:00', '2025-02-01 00:00:00'),
('CCTV-N004', N'國道1號南下001', 'camera', N'{"VideoStream":"rtsp://192.168.1.4/stream1"}', 23.263935, 120.260900, 1, 'active', '2025-02-01 00:00:00', '2025-02-01 00:00:00'),
('CCTV-N005', N'國道1號南下002', 'camera', N'{"VideoStream":"rtsp://192.168.1.5/stream1"}', 22.689950, 120.357000, 1, 'active', '2025-02-01 00:00:00', '2025-02-01 00:00:00');
GO

---

### 6-8. 建立 RoleDevices 資料表

> 💡 **用途**：這是 Roles 和 Devices 的「多對多」中介表，記錄哪個角色可以存取哪些裝置

➡️ 執行 `8_create_table_roledevices.sql`

USE DeviceMonitor;
GO

CREATE TABLE RoleDevices (
    Id       INT IDENTITY(1,1) NOT NULL,
    RoleId   INT               NULL,
    DeviceId INT               NULL,

    CONSTRAINT PK_RoleDevices PRIMARY KEY (Id)
);
GO

---

### 6-9. 插入 RoleDevices 範例資料

➡️ 執行 `9_insert_table_roledevices.sql`

USE DeviceMonitor;
GO

INSERT INTO RoleDevices (RoleId, DeviceId)
VALUES
(1, 1),
(1, 2),
(1, 3),
(1, 4),
(1, 5);
GO

➡️ 此範例代表「系統管理者 (RoleId=1)」可存取全部 5 台裝置

---

### 6-10. 建立所有外鍵約束 (Foreign Keys)

> ⚠️ **此步驟必須最後執行**，確保所有資料表與資料都已建立完成

➡️ 執行 `10_set_all_foreign_keys.sql`

USE DeviceMonitor;
GO

-- FK 1: Users.RoleId -> Roles.Id
ALTER TABLE Users
ADD CONSTRAINT FK_Users_Roles
    FOREIGN KEY (RoleId) REFERENCES Roles(Id);
GO

-- FK 2: RoleDevices.RoleId -> Roles.Id
ALTER TABLE RoleDevices
ADD CONSTRAINT FK_RoleDevices_Roles
    FOREIGN KEY (RoleId) REFERENCES Roles(Id);
GO

-- FK 3: RoleDevices.DeviceId -> Devices.Id
ALTER TABLE RoleDevices
ADD CONSTRAINT FK_RoleDevices_Devices
    FOREIGN KEY (DeviceId) REFERENCES Devices(Id);
GO

➡️ 執行後重新整理，在各資料表的 **Keys** 節點下應可看到：
- `Users` > Keys > `FK_Users_Roles`
- `RoleDevices` > Keys > `FK_RoleDevices_Roles`、`FK_RoleDevices_Devices`

---

### 6-11. 補充：為何 FK 要最後建立？

建立 FK 時，資料庫會立即驗證**現有資料的參照完整性**
若先設 FK 再插入資料，插入順序不對就會報錯
例如：
- 先建 `FK_Users_Roles` 再插入 Users 資料 → 插入時 Roles 必須已有對應資料，否則報錯
- **最安全的做法**：建完所有表 → 插完所有資料 → 最後統一加 FK

---

### 6-12. 補充：若無加上外鍵約束會如何？
**1. 孤兒資料 (Orphaned Data)**
刪除 Roles 中某個角色後，Users 中仍有 `RoleId` 指向已不存在的角色，JOIN 查詢時會找不到角色名稱，導致顯示錯誤甚至程式崩潰

**2. 無效的關聯 (Invalid References)**
沒有約束時，可以隨意將 Users 的 `RoleId` 設為 999，即使 Roles 表中根本沒有 Id=999 的資料，資料庫也不會攔截

**3. 必須靠程式碼手動檢查**
後端程式（C# / Java）需要撰寫大量額外判斷邏輯，且若有多個系統同時操作同一資料庫，只要任一系統沒寫好，資料就會出錯

---

### ref
[SQL Server資料庫設計教學](https://youtu.be/19TsIFHaEec?si=GiVEibPv1qULfjDR)