สร้างค่าอัตโนมัติ

เสร็จสมบูรณ์เมื่อ

คุณอาจจําเป็นต้องสร้างค่าตามลําดับสําหรับหนึ่งคอลัมน์ในตารางที่ระบุโดยอัตโนมัติ Transact-SQL มีสองวิธีในการทําเช่นนี้: ใช้คุณสมบัติ IDENTITY กับคอลัมน์ที่ระบุในตาราง หรือกําหนดวัตถุ SEQUENCE และใช้ค่าที่สร้างขึ้นโดยวัตถุนั้น

คุณสมบัติ IDENTITY

หากต้องการใช้คุณสมบัติ IDENTITY ให้กําหนดคอลัมน์โดยใช้ชนิดข้อมูลตัวเลขที่มีมาตราส่วนของ 0 (ซึ่งหมายถึงจํานวนเต็มเท่านั้น) และรวมคําสําคัญ IDENTITY ชนิดที่อนุญาตได้แก่ ชนิดจํานวนเต็มและชนิดทศนิยมที่คุณกําหนดมาตราส่วน 0 อย่างชัดเจน

ข้อมูลเริ่มต้นที่เลือกได้ (ค่าเริ่มต้น) และยังสามารถระบุการเพิ่ม (ค่าขั้นตอน) ได้ ออกจากเมล็ดและส่วนที่เพิ่มขึ้นจะตั้งค่าทั้งคู่เป็น 1

หมายเหตุ

คุณสมบัติ IDENTITY ถูกระบุแทนที่ด้วยการระบุ NULL หรือ NOT NULL ในข้อกําหนดของคอลัมน์ คอลัมน์ใด ๆ ที่มีคุณสมบัติ IDENTITY จะไม่สามารถเป็น Null ได้โดยอัตโนมัติ คุณสามารถระบุ NOT NULL ได้เพียงเพื่อการจัดทําเอกสารด้วยตนเอง แต่ถ้าคุณระบุคอลัมน์เป็น NULL (ซึ่งหมายความว่า nullable) คําสั่งการสร้างตารางจะสร้างข้อผิดพลาด

มีเพียงหนึ่งคอลัมน์ในตารางเท่านั้นที่อาจมีการตั้งค่าคุณสมบัติ 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 สําหรับคอลัมน์ โดยทั่วไปแล้ว คําสั่ง INSERT ลงในตารางจะไม่ระบุค่าสําหรับคอลัมน์ IDENTITY กลไกจัดการฐานข้อมูลจะสร้างค่าโดยใช้ค่าที่พร้อมใช้งานถัดไปสําหรับคอลัมน์

ตัวอย่างเช่น คุณสามารถแทรกแถวลงในตาราง Sales.Promotion ได้โดยไม่ต้องระบุค่าสําหรับคอลัมน์ PromotionID :

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

โปรดสังเกตว่าแม้ว่าส่วนคําสั่ง VALUES จะไม่รวมค่าสําหรับคอลัมน์ PromotionID แต่คุณไม่จําเป็นต้องระบุรายการคอลัมน์ในคําสั่ง INSERT - คอลัมน์ข้อมูลประจําตัวได้รับการยกเว้นจากข้อกําหนดนี้

ถ้าแถวนี้เป็นแถวแรกที่แทรกลงในตาราง ผลลัพธ์จะเป็นแถวใหม่ดังนี้:

รหัสการเพิ่มระดับ

ชื่อการส่งเสริมการขาย

StartDate

ProductModelID

ส่วนลด

หมาย เหตุ

1

การขายการกวาดล้าง

2021-01-01T00:00:00

23

0.1

ส่วนลด 10%

เมื่อตารางถูกสร้างขึ้น จะไม่มีการตั้งค่าเริ่มต้นหรือการเพิ่มค่าสําหรับคอลัมน์ IDENTITY ดังนั้นแถวแรกจะถูกแทรกด้วยค่า 1 แถวถัดไปที่จะแทรกจะถูกกําหนดให้กําหนดค่า PromotionID เป็น 2 และอื่น ๆ

การดึงค่าข้อมูลประจําตัว

หากต้องการส่งกลับค่า IDENTITY ที่กําหนดไว้ล่าสุดภายในเซสชันและขอบเขตเดียวกัน ให้ใช้ฟังก์ชัน SCOPE_IDENTITY แบบนี้:

SELECT SCOPE_IDENTITY();

ฟังก์ชัน SCOPE_IDENTITY ส่งกลับค่าข้อมูลประจําตัวล่าสุดที่สร้างขึ้นในขอบเขตปัจจุบันสําหรับตารางใด ๆ ถ้าคุณต้องการค่าข้อมูลประจําตัวล่าสุดในตารางที่ระบุ คุณสามารถใช้ฟังก์ชัน IDENT_CURRENT ดังนี้:

SELECT IDENT_CURRENT('Sales.Promotion');

การแทนที่ค่าข้อมูลประจําตัว

ถ้าคุณต้องการแทนที่ค่าที่สร้างขึ้นโดยอัตโนมัติและกําหนดค่าเฉพาะให้กับคอลัมน์ IDENTITY ก่อนอื่นคุณต้องเปิดใช้งานการแทรกข้อมูลประจําตัวโดยใช้คําสั่ง 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 ไม่เหมาะสมสําหรับค่าพิกัดในหลายตารางภายในฐานข้อมูล ตัวอย่างเช่น สมมติว่าองค์กรของคุณแยกความแตกต่างระหว่างยอดขายโดยตรงและยอดขายไปยังผู้จําหน่าย และต้องการจัดเก็บข้อมูลสําหรับยอดขายเหล่านี้ในตารางแยกต่างหาก การขายทั้งสองประเภทอาจต้องใช้หมายเลขใบแจ้งหนี้ที่ไม่ซ้ํากัน และคุณอาจต้องการหลีกเลี่ยงการทําซ้ําค่าเดียวกันสําหรับการขายสองชนิดที่แตกต่างกัน โซลูชันหนึ่งสําหรับข้อกําหนดนี้คือการรักษากลุ่มของค่าตามลําดับที่ไม่ซ้ํากันในทั้งสองตาราง

Reseeding คอลัมน์ข้อมูลประจําตัว

ในบางครั้ง คุณจะต้องรีเซ็ตหรือข้ามค่าข้อมูลประจําตัวสําหรับคอลัมน์ เมื่อต้องการทําเช่นนี้ คุณจะ "แก้ไข" คอลัมน์โดยใช้ฟังก์ชัน DBCC CHECKIDENT คุณสามารถใช้ฟังก์ชันนี้เพื่อข้ามค่าจํานวนมาก หรือเพื่อรีเซ็ตค่าข้อมูลประจําตัวถัดไปเป็น 1 หลังจากที่คุณลบแถวทั้งหมดในตารางแล้ว สําหรับรายละเอียดทั้งหมดโดยใช้ DBCC CHECKIDENT โปรดดูเอกสารประกอบการอ้างอิงTransact-SQL

อนุกรม

ใน Transact-SQL คุณสามารถใช้ออบเจ็กต์ลําดับเพื่อกําหนดค่าลําดับใหม่ได้อย่างอิสระจากตารางที่ระบุ ออบเจ็กต์ลําดับถูกสร้างขึ้นโดยใช้คําสั่ง 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 สําหรับการเติมค่าอัตโนมัติ ให้คํานึงถึงประเด็นต่อไปนี้:

• ใช้ SEQUENCE ถ้าแอปพลิเคชันของคุณจําเป็นต้องมีการแชร์ชุดตัวเลขชุดเดียวระหว่างหลายตารางหรือหลายคอลัมน์ภายในตาราง

• SEQUENCE ช่วยให้คุณสามารถเรียงลําดับค่าตามคอลัมน์อื่น ค่า NEXT สําหรับ คอนสตรัคเตอร์ สามารถใช้ส่วนคําสั่ง OVER เพื่อระบุคอลัมน์การเรียงลําดับ คําสั่งย่อย OVER รับประกันว่าค่าที่ส่งกลับจะถูกสร้างขึ้นตามลําดับของส่วนคําสั่ง OVER BY ของส่วนคําสั่ง OVER ฟังก์ชันนี้ยังช่วยให้คุณสามารถสร้างหมายเลขแถวสําหรับแถวได้เนื่องจากมีการส่งกลับใน SELECT ในตัวอย่างต่อไปนี้ ตาราง Production.Product จะเรียงลําดับตามคอลัมน์ Name และคอลัมน์แรกที่ส่งกลับคือตัวเลขตามลําดับ

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

  แม้ว่าคําสั่งก่อนหน้าแค่เลือกค่า SEQUENCE เพื่อแสดงค่ายังคงถูก 'ใช้ขึ้น' และค่าลําดับที่แสดงจะไม่พร้อมใช้งานอีกต่อไป ถ้าคุณเรียกใช้ SELECT หลาย ๆ ครั้ง คุณจะได้รับค่าลําดับที่แตกต่างกันในแต่ละครั้ง

• ใช้ SEQUENCE หากแอปพลิเคชันของคุณต้องการกําหนดตัวเลขหลายหมายเลขในเวลาเดียวกัน ตัวอย่างเช่น แอปพลิเคชันต้องจองหมายเลขห้าหมายเลขตามลําดับ การขอค่าข้อมูลประจําตัวอาจส่งผลให้เกิดช่องว่างในชุดข้อมูลหากกระบวนการอื่น ๆ ได้รับตัวเลขในเวลาเดียวกัน คุณสามารถใช้ ขั้นตอน sp_sequence_get_range ระบบ เพื่อดึงข้อมูลตัวเลขหลายตัวตามลําดับในครั้งเดียวได้

• SEQUENCE ช่วยให้คุณสามารถเปลี่ยนข้อมูลจําเพาะของลําดับ เช่น ค่าที่เพิ่มขึ้น

• ค่าข้อมูลประจําตัวได้รับการป้องกันจากการอัปเดต ถ้าคุณพยายามอัปเดตคอลัมน์ด้วยคุณสมบัติ IDENTITY คุณจะได้รับข้อผิดพลาด