建表範例回顧
CREATE TABLE yg1 (
姓名 VARCHAR(8),
性別 CHAR(2),
年齡 INT,
籍貫 VARCHAR(5),
電話 VARCHAR(11),
工資 MONEY,
婚否 BIT
);
SQL Server 資料型別完整分類表
1. 數值型
| 型別 | 範圍 | 位元組 | 用途 |
|---|
| BIGINT | -2^63 ~ 2^63-1 | 8 | 極大整數 (身分證號、大數量計數) |
| INT | -2^31 ~ 2^31-1 | 4 | 標準整數 (主鍵、數量、狀態碼) |
| SMALLINT | -2^15 ~ 2^15-1 | 2 | 小整數 (年齡、排序、小範圍代碼) |
| TINYINT | 0 ~ 255 | 1 | 極小非負整數 (布林替代、極小枚舉) |
| BIT | 0, 1, NULL | 1 (位元壓縮) | 邏輯真/假 (已婚、啟用、刪除標記) |
| DECIMAL / NUMERIC(p,s) | -10^38+1 ~ 10^38-1 | 5-17 | 精確定點數 (金額、匯率、比率) **首選** |
| MONEY / SMALLMONEY | -922,337... ~ 922,337... | 8 / 4 | 貨幣專用 (含符號顯示)，精度固定 4 位小數 |
| FLOAT(n) / REAL | -1.79E+308 ~ 1.79E+308 | 4/8 | 近似數值 (科學計算、經緯度)，**不可用於金融計算** |
2. 日期時間型
| 型別 | 範圍 | 精度 | 位元組 | 備註 |
|---|
| DATETIME | 1753-01-01 ~ 9999-12-31 | 3.33 毫秒 | 8 | 舊版通用，精度較低 |
| SMALLDATETIME | 1900-01-01 ~ 2079-06-06 | 1 分鐘 | 4 | 已棄用建議 |
| DATE | 0001-01-01 ~ 9999-12-31 | 1 天 | 3 | SQL 2008+，僅日期 |
| TIME(n) | 00:00:00 ~ 23:59:59.9999999 | 100 奈秒 | 3-5 | SQL 2008+，僅時間 |
| DATETIME2(n) | 0001-01-01 ~ 9999-12-31 | 100 奈秒 | 6-8 | SQL 2008+，**推薦替代 DATETIME** |
| DATETIMEOFFSET(n) | 含時區位移 | 100 奈秒 | 8-10 | SQL 2008+，跨時區應用必備 |
3. 字串型
| 型別 | 長度 | 儲存特性 | 適用場景 |
|---|
| CHAR(n) | 固定 1-8000 | 不足補空格，定長 | 固定長度代碼 (身分證、國碼、MD5) |
| VARCHAR(n) | 可變 1-8000 | 實際長度+2位元組開銷 | 一般文字、描述、名稱 |
| VARCHAR(MAX) | 可變 0-2^31-1 | 大值型別 (LOB)，行外儲存 | 長文本、JSON、XML、日誌 |
| NCHAR(n) | 固定 1-4000 | Unicode 雙位元組 | 固定長度多語系 |
| NVARCHAR(n) | 可變 1-4000 | Unicode 雙位元組 | **多語系文字首選** |
| NVARCHAR(MAX) | 可變 0-2^30-1 | Unicode LOB | 長多語系文本 |
| TEXT / NTEXT / IMAGE | 已棄用 | LOB 舊型別 | **勿用新開發**，改用 *VARCHAR(MAX)/NVARCHAR(MAX)/VARBINARY(MAX) |
4. 二進位型
- `BINARY(n)` 固定、`VARBINARY(n)` 可變、`VARBINARY(MAX)` 大物件。用於檔案流、圖片、加密資料、雜湊值。
5. 特殊型別
- `UNIQUEIDENTIFIER` (GUID) 16 位元組，`NEWID()`/`NEWSEQUENTIALID()` 產生。
- `XML` 型別 (SQL 2005+)，支援 Schema 集合、索引、XQuery。
- `HIERARCHYID` (SQL 2008+)，階層結構編碼 (組織架構、目錄樹)。
- `GEOMETRY` / `GEOGRAPHY` (SQL 2008+)，空間資料 (GIS)。
- `TABLE` 型別，預存程序/函數參數傳遞表值參數 (TVP)。
DDL 修改表結構核心指令
-- 1. 新增欄位
ALTER TABLE yg ADD 郵編 CHAR(6); -- 必須指定型別
-- 2. 刪除欄位
ALTER TABLE yg DROP COLUMN 年齡;
-- 3. 修改欄位屬性 (型別、長度、NULL/NOT NULL、DEFAULT)
ALTER TABLE yg ALTER COLUMN 性別 CHAR(10);
-- 4. 重新命名欄位 (系統預存程序)
EXEC sp_rename 'yg.年齡', 'age', 'COLUMN';
-- 5. 重新命名表
EXEC sp_rename 'yg', 'newyg'; -- 物件類型預設 OBJECT
-- 6. 刪除表
DROP TABLE yg;
DROP TABLE yg, yg1, yg2; -- 多表同時刪除
實戰避坑指南
- ALTER COLUMN 型別變更：縮小長度/精度前確認現有資料不截斷；`INT → SMALLINT` 需確認值在範圍內。
2. 加 NOT NULL：現有 NULL 值列必須先 `UPDATE ... SET col=預設值 WHERE col IS NULL`，或加 `DEFAULT` 約束再 `ALTER COLUMN ... NOT NULL`。
3. sp_rename 限制：僅改名稱，**不改變物件 ID、權限、相依物件 (視圖/SP 需手動刷新)**。
4. 大表 DDL：`ALTER TABLE` 可能鎖表長時間。SQL Server 2016+ 支援 `ONLINE=ON` (Enterprise) 或 `RESUMABLE=ON` 可恢復線上操作。
5. 型別選型原則：能用 INT 別用 BIGINT，能用 DECIMAL 別用 FLOAT，能用 VARCHAR 別用 CHAR，多語系必用 NVARCHAR，金額必用 DECIMAL/MONEY，新專案用 DATETIME2。
小結
精通資料型別特性與 DDL 修改指令，是資料庫設計與演進的基本功。**型別選對，效能、空間、正確性三贏**；DDL 操作謹慎規劃、備份先行、驗證後行。
