CREATE TABLE [schema_name.]table_name (
column_name data_type [NULL | NOT NULL]
[DEFAULT default_expr]
[IDENTITY(seed, increment)]
[PRIMARY KEY | UNIQUE]
[CHECK (logical_expression)]
[COLLATE collation_name],
...,
[CONSTRAINT constraint_name PRIMARY KEY (col1, col2)],
[CONSTRAINT constraint_name FOREIGN KEY (col) REFERENCES ref_table(ref_col) ON DELETE CASCADE],
[CONSTRAINT constraint_name CHECK (col > 0)],
[CONSTRAINT constraint_name UNIQUE (col1, col2)]
);
CREATE TABLE dbo.Orders (
OrderID BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY,
OrderNo VARCHAR(30) NOT NULL UNIQUE,
CustomerID INT NOT NULL,
OrderDate DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
Status TINYINT NOT NULL DEFAULT 0 CHECK (Status BETWEEN 0 AND 5),
TotalAmount DECIMAL(18,4) NOT NULL DEFAULT 0 CHECK (TotalAmount >= 0),
Currency CHAR(3) NOT NULL DEFAULT 'TWD',
Remark NVARCHAR(MAX) NULL,
CreatedBy INT NOT NULL,
CreatedAt DATETIME2(3) NOT NULL DEFAULT SYSUTCDATETIME(),
UpdatedBy INT NULL,
UpdatedAt DATETIME2(3) NULL,
RowVersion ROWVERSION NOT NULL, -- 樂觀鎖
CONSTRAINT FK_Orders_Customer FOREIGN KEY (CustomerID) REFERENCES dbo.Customers(CustomerID)
);
-- 非叢集索引支援常用查詢
CREATE NONCLUSTERED INDEX IX_Orders_Customer_Date
ON dbo.Orders(CustomerID, OrderDate DESC)
INCLUDE (Status, TotalAmount);
| 需求 | 推薦型別 | 備註 |
|---|---|---|
| 自動遞增主鍵 | BIGINT IDENTITY / INT IDENTITY | 大數據量用 BIGINT |
| 精確金額 | DECIMAL(18,4) 或 DECIMAL(19,4) | 避免 FLOAT/REAL 浮點誤差 |
| 高精度時間 | DATETIME2(n) | n=3 毫秒、n=7 100ns,優於舊 DATETIME |
| 大型文字 | NVARCHAR(MAX) | Unicode、2GB 上限 |
| 二進位大物件 | VARBINARY(MAX) | FILESTREAM 另議 |
| 布林旗標 | BIT | 1 位元儲存 |
| 唯一識別碼 | UNIQUEIDENTIFIER (GUID) | NEWID() 隨機、NEWSEQUENTIALID() 有序 |
FullName AS (FirstName + ' ' + LastName) PERSISTED 可建索引。ColName INT SPARSE NULL,大量 NULL 節省空間。ON [PRIMARY] TEXTIMAGE_ON [FG_BLOB] 將 LOB 資料分離至不同磁碟。WITH (DATA_COMPRESSION = PAGE) 節省空間並提升 I/O。CREATE TABLE ... WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) 適用高併發熱點表。IF NOT EXISTS 或 IF OBJECT_ID('dbo.Table','U') IS NULL 保冪等。ONLINE = ON(Enterprise)。V{version}__{description}.sql,工具如 Flyway、Liquibase、DbUp。遵循規範建立表結構,能減少後期重構成本,提升查詢效能與資料品質。