SQL Server Useful Create Script (萬用新增資料表語法參考)

2024-01-29

筆記 SQL Server 萬用的新增資料表語法參考 (Default, Foreign Key, Reference, Relationship)

SQL Server Logo

說明

Common Columns

CREATE TABLE [dbo].[A](
  [Id] int IDENTITY(1,1) NOT NULL,
  [InsertDateTime] datetime2 DEFAULT(getdate()) NOT NULL,
  [UpdateDateTime] datetime2 DEFAULT(getdate()) NULL,
  [CategoryId] int NULL,
  [CreatedBy] nvarchar(50) NULL,
  [UpdatedBy] nvarchar(50) NULL,
  [IsActive] bit NULL,
  [IsDeleted] bit NULL
  CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED ([Id] ASC) 
)

如果要用資料表來記錄 HTML Element 以下是常用到的欄位：

CREATE TABLE [dbo].[HTMLElements](
  [Id] int IDENTITY(1,1) NOT NULL,
  [ElementName] varchar(100) NULL,
  [ElementValue] varchar(100) NULL,
  [Style] varchar(200) NULL,
  [Href] varchar(1000) NULL,
  [ClassName] varchar(200) NULL,
  [DataType] varchar(50) NULL,
  [IsRequired] bit NULL,
  [MaxLength] int NULL,
  [Placeholder] nvarchar(200) NULL
  CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED ([Id] ASC) 
)

One to Many

CREATE TABLE [dbo].[A](
  [Id] int IDENTITY(1,1) NOT NULL,
  [Text] nvarchar(50) NULL
  CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED ([Id] ASC)
)

CREATE TABLE [dbo].[B](
  [Id] int IDENTITY(1,1) NOT NULL,
  [AId] int NOT NULL,
  [TimeStamp] datetime2 DEFAULT(getdate())
  CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED ([Id] ASC) 
  CONSTRAINT [FK_A_Id] FOREIGN KEY([AId]) REFERENCES [dbo].[A] ([Id])
)

以上 script 說明如何建立 A 資料表對 B 資料表一對多的關係，實現的方式是透過 B 資料表加入 A 資料表的 ID 欄位，並且透過設定 Constraint, Foreign Key, References 來達成 外鍵與關連設定。

基本的 Primary Key 設定由要扮演主鍵的欄位加入 IDENTITY(1,1) 以及 NOT NULL 並且再設定 Constraint, Primary Key Clustered 來達成 主鍵設定。

此外因為主鍵會建立索引 (Index) 可以再搭配 With 來設定索引細節：

CREATE TABLE [dbo].[B](
  [Id] int IDENTITY(1,1) NOT NULL,
  [AId] int NOT NULL,
  [TimeStamp] datetime2 DEFAULT(getdate())
  CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED ([Id] ASC)
    WITH (
      PAD_INDEX = OFF, 
      STATISTICS_NORECOMPUTE = OFF, 
      IGNORE_DUP_KEY = OFF, 
      ALLOW_ROW_LOCKS = ON, 
      ALLOW_PAGE_LOCKS = ON, 
      OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF)
  CONSTRAINT [FK_A_Id] FOREIGN KEY([AId]) REFERENCES [dbo].[A] ([Id])
)

Multiple Column As Primary Key

而如果想要使用多個欄位構成主鍵，可以使用以下方式：

CREATE TABLE [dbo].[Products] (
  [ProductID] INT IDENTITY(1,1),
  [StoreID] INT IDENTITY(1,1),
  [ProductName] VARCHAR(255),
  [Price] DECIMAL(10, 2),
  CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (ProductID, StoreID)
);

One to One

有兩種方式可以達成一對一的資料表關係，第一種方式使用 Constraint, Foreign key, References 的方式，兩張 Table 必須要有相同的 Primary Key。

CREATE TABLE [dbo].[A](
    [Id] int IDENTITY(1,1) NOT NULL,
    [Text] nvarchar(50) NULL,
    CONSTRAINT [PK_A] PRIMARY KEY CLUSTERED ([Id] ASC)
)

CREATE TABLE [dbo].[B](
    [Id] int NOT NULL,
    [Col1] varchar(50) NULL,
    [Col2] varchar(50) NULL,
    CONSTRAINT [PK_B] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_B_Id] FOREIGN KEY([Id]) REFERENCES [dbo].[A]([Id])
)

第二種方式使用 Constraint, Unique 的方式，兩張 Table 可以有不同的 Primary Key。

CREATE TABLE [dbo].[A2](
    [Id] int IDENTITY(1,1) NOT NULL,
    [Text] nvarchar(50) NULL,
    CONSTRAINT [PK_A2] PRIMARY KEY CLUSTERED ([Id] ASC)
)

CREATE TABLE [dbo].[B2](
    [Id] int IDENTITY(1,1) NOT NULL,
    [AId] int NOT NULL,
    [Col1] varchar(50) NULL,
    [Col2] varchar(50) NULL,
    CONSTRAINT [PK_B2] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [UQ_B_AId] UNIQUE ([AId])
)

🧪 兩種方式在 EntityFramework Database First 的支援方式驗證。

參考資料

Primary Key Clustered With Options 說明

PAD_INDEX = OFF: This option controls the padding of the index pages. When set to OFF, SQL Server does not add extra space to index pages. Padding an index can improve performance when inserting data into a table by reducing the need for page splits, but it uses more storage. When set to ON, SQL Server leaves free space on the index pages to accommodate future growth, which is useful for indexes on columns that are frequently updated or inserted into.

PAD_INDEX = OFF：此選項控制索引頁面的填充。設為 OFF 時，SQL Server 不會在索引頁面中添加額外的空間。索引填充可以在向表中插入數據時提高性能，因為它減少了頁面分裂的需要，但會使用更多的存儲空間。設為 ON 時，SQL Server 會在索引頁面留下空閒空間以適應未來的增長，這對於頻繁更新或插入的列上的索引很有用。

STATISTICS_NORECOMPUTE = OFF: This setting controls the automatic updating of index statistics. When set to OFF, SQL Server automatically updates the statistics as data changes. Statistics are crucial for the query optimizer to make informed decisions about the best query plan. When set to ON, it prevents automatic updates, which might be useful in very specific scenarios where the data distribution does not change often, and manual updates are preferred for performance reasons.

STATISTICS_NORECOMPUTE = OFF：此設置控制索引統計信息的自動更新。設為 OFF 時，SQL Server 會隨著數據變化自動更新統計信息。統計信息對於查詢優化器來說是至關重要的，它可以做出最佳查詢計劃的決定。設為 ON 時，會阻止自動更新，這在數據分佈不經常改變且優先考慮性能的特定場景中很有用。

IGNORE_DUP_KEY = OFF: This setting applies to unique indexes or primary keys. When OFF, an error is raised if a new row insertion or an update of a key column would result in a duplicate key. When set to ON, instead of an error, a warning message is issued, and the duplicate row is not added, but the transaction continues. This can be useful for operations where duplicates are expected but not critical.

IGNORE_DUP_KEY = OFF：此設置適用於唯一索引或主鍵。當設為 OFF 時，如果新行插入或鍵列的更新將導致重複的鍵，則會引發錯誤。設為 ON 時，不會出現錯誤，而是發出警告消息，且不添加重複行，但事務繼續。這對於預期但不關鍵的重複是有用的。

ALLOW_ROW_LOCKS = ON: This option enables row-level locking for the index. Row-level locks can increase concurrency by allowing multiple transactions to access different rows of the table simultaneously, even within the same page.

ALLOW_ROW_LOCKS = ON：此選項啟用索引的行級鎖定。行級鎖可以通過允許多個事務同時訪問表的不同行（即使在同一頁面內）來提高併發性。

ALLOW_PAGE_LOCKS = ON: This setting allows page-level locking for the index. Page locks are more efficient than row locks when large numbers of rows are affected by a single transaction, as fewer locks are needed. However, it can reduce concurrency since a single lock can block access to hundreds of rows at once.

ALLOW_PAGE_LOCKS = ON：此設置允許索引的頁面級鎖定。當單個事務影響大量行時，頁面鎖比行鎖更有效，因為需要的鎖更少。然而，它可以減少並發性，因為單個鎖可以一次性阻塞對成百上千行的訪問。

OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF: This option is designed to improve the performance of workloads that use sequential keys, such as those found in identity columns or other monotonically increasing sequences. When set to ON, it helps mitigate the performance impact of last-page insert contention, which can occur with these types of keys. This is especially beneficial for high-concurrency environments. When OFF, this optimization is not applied.

OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF：此選項旨在提高使用順序鍵（如身份列或其他單調遞增序列中的鍵）的工作負載的性能。設為 ON 時，它有助於緩解最後頁面插入爭用的性能影響，這種爭用可能發生在這類鍵上。這對於高併發環境特別有益。當設為 OFF 時，不會應用此優化。

SQL Server Name Convention and T-SQL Programming Style

相關連結

SQL Server Integrated Service 初探

SQL Server 閃電般快速查詢指南⚡

SQL Server 周邊工具彙整筆記

SQL Server 學習資源筆記

#Database #SQLServer