萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

SQL Server 建立資料表完整語法與最佳實踐範例

標準 CREATE TABLE 語法

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 另議
布林旗標BIT1 位元儲存
唯一識別碼UNIQUEIDENTIFIER (GUID)NEWID() 隨機、NEWSEQUENTIALID() 有序

進階技巧

  1. 計算欄位:FullName AS (FirstName + ' ' + LastName) PERSISTED 可建索引。
  2. 稀疏欄位:ColName INT SPARSE NULL,大量 NULL 節省空間。
  3. 檔案群組:ON [PRIMARY] TEXTIMAGE_ON [FG_BLOB] 將 LOB 資料分離至不同磁碟。
  4. 資料壓縮:WITH (DATA_COMPRESSION = PAGE) 節省空間並提升 I/O。
  5. 記憶體優化表:CREATE TABLE ... WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) 適用高併發熱點表。

遷移腳本規範

  • 使用 IF NOT EXISTS 或 IF OBJECT_ID('dbo.Table','U') IS NULL 保冪等。
  • 所有 DDL 包在交易中(隱含),但長時間鎖定大表需謹慎,考慮線上索引建立 ONLINE = ON(Enterprise)。
  • 版本控管:每個變更一個 V{version}__{description}.sql,工具如 Flyway、Liquibase、DbUp。

遵循規範建立表結構,能減少後期重構成本,提升查詢效能與資料品質。

copyright © 萬盛學電腦網 all rights reserved