產生自動值

  • 3 分鐘

您可能需要為特定資料表中的一個資料行自動產生連續值。 Transact-SQL 提供兩種方式來執行此動作：使用 IDENTITY 屬性搭配資料表中的特定資料行，或定義 SEQUENCE 物件，並使用該物件所產生的值。

IDENTITY 屬性

若要使用 IDENTITY 屬性，請使用小數位數為 0 的數值資料類型定義資料行 (表示僅限整數)，並包含 IDENTITY 關鍵字。 允許的類型包括所有整數類型，以及您明確提供小數位數 0 的十進位類型。

您也可以指定選擇性種子 (起始值) 和遞增 (間距值)。 省略種子和遞增會將這兩者設定為 1。

注意

指定 IDENTITY 屬性可取代在資料行定義中指定 NULL 或 NOT NULL 的做法。 具有 IDENTITY 屬性的任何資料行都會自動成為不可為 Null。 您可以只針對自編文件指定 NOT NULL，但如果將資料行指定為 NULL (表示可為 Null)，則建立資料表陳述式將會產生錯誤。

資料表中只有一個資料行可以設定 IDENTITY 屬性；該資料行通常會作為主索引鍵或替代索引鍵。

下列程式碼示範如何建立上一節範例中所使用的 Sales.Promotion 資料表，但這次使用名為 PromotionID 的識別欄位作為主索引鍵：

CREATE TABLE Sales.Promotion
(
PromotionID int IDENTITY PRIMARY KEY,
PromotionName varchar(20),
StartDate datetime NOT NULL DEFAULT GETDATE(),
ProductModelID int NOT NULL REFERENCES Production.ProductModel(ProductModelID),
Discount decimal(4,2) NOT NULL,
Notes nvarchar(max) NULL
);

注意

CREATE TABLE 陳述式的完整詳細資料不在本課程模組範圍內。

將資料插入識別欄位中

為資料行定義 IDENTITY 屬性時，將陳述式插入資料表中通常不會指定識別欄位的值。 資料庫引擎會使用資料行下一個可用的值來產生值。

例如，您可以將資料列插入 Sales.Promotion 資料表中，而不需要指定 PromotionID 資料行的值：

INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.10, '10% discount')

請注意，即使 VALUES 子句未包含 PromotionID 資料行的值，您也無須在 INSERT 子句中指定資料行清單 - 識別資料行無此需求。

如果此資料列是插入資料表中的第一個資料列，結果會是新的資料列，如下所示：

PromotionID

PromotionName

StartDate

ProductModelID

折扣

注意

1

清倉大拍賣

2021-01-01T00:00:00

23

0.1

10% 折扣

建立資料表時，並未針對識別欄位設定任何種子或遞增值，因此第一個要插入的資料列會有值 1。 下一個要插入的資料列會獲指派 PromotionID 值 2，依此類推。

擷取識別值

若要傳回在相同工作階段和範圍中最近指派的識別值，請使用 SCOPE_IDENTITY 函式，如下所示：

SELECT SCOPE_IDENTITY();

SCOPE_IDENTITY 函式會傳回在目前範圍中為任何資料表產生的最新識別值。 如果需要特定資料表中的最新識別值，您可以使用 IDENT_CURRENT 函式，如下所示：

SELECT IDENT_CURRENT('Sales.Promotion');

覆寫識別值

如果想要覆寫自動產生的值，並將特定值指派給識別欄位，您必須先使用 SET IDENTITY INSERT table_name ON 陳述式啟用識別插入。 啟用此選項之後，您即可為識別欄位插入明確值，如同任何其他資料行。 當完成時，您可以使用 SET IDENTITY INSERT table_name OFF 陳述式繼續使用自動識別值，並使用您明確輸入的最後一個值作為種子。

SET IDENTITY_INSERT SalesLT.Promotion ON;

INSERT INTO SalesLT.Promotion (PromotionID, PromotionName, ProductModelID, Discount)
VALUES
(20, 'Another short sale',37, 0.3);

SET IDENTITY_INSERT SalesLT.Promotion OFF;

如您所瞭解，IDENTITY 屬性可用來為資料表中的資料行產生一連串的值。 不過，IDENTITY 屬性不適合用來在資料庫中的多個資料表之間協調值。 例如，假設您的組織想要區分直接銷售與轉銷商銷售，並想要將這些銷售資料儲存在個別的資料表中。 這兩種銷售類型可能需要唯一的發票號碼，因此您可能想要避免在兩種不同的銷售之間重複相同值。 這項需求的一個解決方法就是在兩個資料表之間維護一個不重複連續值的集區。

重新建立識別資料行

有時候，您必須重設或略過資料行的識別值。 若要這樣做，您將使用 DBCC CHECKIDENT 函式來「重新建立」資料行。 您可使用此函式來略過許多值，或在刪除資料表中的所有資料列之後，將下一個識別值重設為 1。 如需使用 DBCC CHECKIDENT 的完整詳細資料，請參閱 Transact-SQL 參考文件。

SEQUENCE

在 Transact-SQL 中，您可以使用序列物件提供獨立於特定資料表的新連續值。 SEQUENCE 物件是使用 CREATE SEQUENCE 陳述式所建立，並可選擇性地提供資料類型 (必須是整數類型或是小數位數為 0 的十進位或數值)、起始值、遞增值、最大值，以及其他與效能相關的選項。

CREATE SEQUENCE Sales.InvoiceNumber AS INT
START WITH 1000 INCREMENT BY 1;

若要從序列中擷取下一個可用的值，請使用 NEXT VALUE FOR 建構，如下所示：

INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);

IDENTITY 或 SEQUENCE

在決定要使用 IDENTITY 資料行或 SEQUENCE 物件做為自動填入值時，請謹記下列要點：

  • 如果應用程式需要在多個資料表或資料表中多個資料行之間共用一連串數字，請使用 SEQUENCE。

  • SEQUENCE 可讓您依另一個資料行排序值。 NEXT VALUE FOR 建構可以使用 OVER 子句指定排序資料行。 OVER 子句會確保傳回的值都按照 OVER 子句之 ORDER BY 子句的順序來產生。 這項功能也可讓您產生資料列的資料列號碼，因為 SELECT 中將傳回這些號碼。 在下列範例中，Production.Product 資料表是依 Name 資料行排序，而第一個傳回的資料行是序號。

    SELECT NEXT VALUE FOR dbo.Sequence OVER (ORDER BY Name) AS NextID,
    ProductID,
    Name
FROM Production.Product;

    雖然上述陳述式只是選取 SEQUENCE 值供顯示之用，但仍會「用掉」值，因此顯示過的 SEQUENCE 值將無法再使用。 如果多次執行上述 SELECT，您會每次取得不同的 SEQUENCE 值。

  • 如果應用程式需要同時指派多個數字，請使用 SEQUENCE。 例如，應用程式需要保留五個序號。 如果同時針對其他處理序發出編號，要求識別值可能會在序列中產生間距。 您可以使用 sp_sequence_get_range 系統程序，一次擷取序列中的幾個數字。

  • SEQUENCE 可讓您變更序列的規格，例如遞增值。

  • 識別值會受到保護避免更新。 如果您嘗試更新具有 IDENTITY 屬性的資料行，就會收到錯誤。