跟我學 MS SQL Server（五）：建立與修改資料表 DDL 完整實戰指南

一、資料庫與資料表建立基礎

1. 建立資料庫

CREATE DATABASE Catalog
ON PRIMARY (
    NAME = Catalog_Data,
    FILENAME = 'D:\Data\Catalog.mdf',
    SIZE = 100MB, MAXSIZE = 1GB, FILEGROWTH = 50MB
)
LOG ON (
    NAME = Catalog_Log,
    FILENAME = 'D:\Log\Catalog.ldf',
    SIZE = 50MB, MAXSIZE = 500MB, FILEGROWTH = 25MB
);

權限：需 `CREATE DATABASE` 權限或 `dbcreator` 固定伺服器角色。

2. 建立資料表標準範本

CREATE TABLE dbo.Products (
    ProductID   INT IDENTITY(1,1) NOT NULL,
    ProductName NVARCHAR(100) NOT NULL,
    Color       NVARCHAR(20) NULL,
    Description NVARCHAR(MAX) NULL,
    Size        DECIMAL(8,2) NULL,
    Weight      DECIMAL(8,3) NULL,
    StandardCost MONEY NULL,
    ListPrice   MONEY NULL,
    SellStartDate DATETIME NOT NULL DEFAULT GETDATE(),
    SellEndDate   DATETIME NULL,
    DiscontinuedDate DATETIME NULL,
    rowguid     UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID(),
    ModifiedDate DATETIME NOT NULL DEFAULT GETDATE(),

    CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (ProductID),
    CONSTRAINT CK_Products_ListPrice CHECK (ListPrice >= 0),
    CONSTRAINT CK_Products_StandardCost CHECK (StandardCost >= 0),
    CONSTRAINT UQ_Products_Name UNIQUE NONCLUSTERED (ProductName)
);

二、常用資料類型速查表

三、進階 DDL 選項

1. 身分識別欄位

ProductID INT IDENTITY(1,1) NOT NULL
-- 種子 1，遞增 1；可用 DBCC CHECKIDENT 重置

2. 主鍵與叢集索引

CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (ProductID)
-- 主鍵預設建立叢集索引；可指定 NONCLUSTERED

3. 唯一約束 (替代唯一索引)

CONSTRAINT UQ_Products_Name UNIQUE NONCLUSTERED (ProductName)
-- 自動建立唯一非叢集索引

4. 檢查約束

CONSTRAINT CK_Products_ListPrice CHECK (ListPrice >= 0)
-- 可包含多欄位邏輯：CHECK (SellEndDate IS NULL OR SellEndDate > SellStartDate)

5. 預設約束

SellStartDate DATETIME NOT NULL DEFAULT GETDATE()
ModifiedDate DATETIME NOT NULL DEFAULT GETDATE()
rowguid UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID()

6. 外鍵約束

ALTER TABLE dbo.OrderDetails ADD CONSTRAINT FK_OrderDetails_Products
FOREIGN KEY (ProductID) REFERENCES dbo.Products(ProductID)
ON DELETE NO ACTION ON UPDATE CASCADE;

四、修改資料表：ALTER TABLE 完整語法

五、索引建立與管理

1. 建立非叢集索引

CREATE NONCLUSTERED INDEX IX_Products_Name ON dbo.Products (ProductName)
INCLUDE (Color, ListPrice);

2. 篩選索引 (2008+)

CREATE NONCLUSTERED INDEX IX_Products_Active ON dbo.Products (ProductName)
WHERE DiscontinuedDate IS NULL;

3. 索引維護

-- 重建 (離線/線上)
ALTER INDEX ALL ON dbo.Products REBUILD WITH (ONLINE = ON, FILLFACTOR = 90);
-- 重組 (輕量)
ALTER INDEX ALL ON dbo.Products REORGANIZE;

六、刪除資料表與資料庫

1. 刪除表

DROP TABLE dbo.Products;
-- 含所有資料、索引、約束、觸發器、權限
-- 相依外鍵需先刪除或用 CASCADE (非標準)

2. 刪除資料庫

DROP DATABASE Catalog;
-- 含所有物件、檔案、備份歷史 (msdb 保留)
-- 需獨佔存取，先 `ALTER DATABASE SET SINGLE_USER WITH ROLLBACK IMMEDIATE`

3. 清空表保留結構

TRUNCATE TABLE dbo.Products;
-- 最小記錄、重置身分識別、不可觸發觸發器、需無外鍵參考
-- 或 `DELETE FROM Products` (全記錄、觸發觸發器、不重置身分識別)

七、生產環境 DDL 部署鐵律

1. 版控所有 DDL：Git 管理 `.sql` 檔，含 `UP`/`DOWN` 遷移腳本
2. 測試環境完整演練：資料量、鎖定時間、應用相容性
3. 維護視窗執行**：低峰期，通知利害關係人
4. 備份先行**：完整備份 + 記錄備份，驗證可還原
5. 可回滾腳本**：每個 `ALTER`/`DROP` 對應 `DOWN` 腳本
6. 監控鎖阻塞**：`sp_WhoIsActive`、擴充事件監控 `lock_escalation`

八、常見 DDL 錯誤與修正

下一篇將講解多表查詢：`JOIN` 類型 (INNER/LEFT/RIGHT/FULL/CROSS)、`APPLY`、`UNION`/`EXCEPT`/`INTERSECT`、子查詢相關/非相關、CTE 遞歸查詢完整實戰。