คอลัมน์ IDENTITY ใน Fabric คลังข้อมูล

นําไปใช้กับ:✅ Warehouse ใน Microsoft Fabric

ใน Fabric คลังข้อมูล IDENTITY คอลัมน์จะสร้างค่าตัวเลขใหม่โดยอัตโนมัติเมื่อคุณแทรกแถวใหม่ลงในตาราง

คีย์ตัวแทนเป็นตัวระบุที่ใช้ในคลังข้อมูลเพื่อแยกแยะแถวโดยไม่ขึ้นกับคีย์ธรรมชาติ บทความนี้อธิบายวิธีสร้างและจัดการกุญแจตัวแทนโดยใช้ IDENTITY, รวมถึงการแทรกค่าที่ชัดเจนและการรีซีด

เหตุใดจึงต้องใช้คอลัมน์ IDENTITY

IDENTITY คอลัมน์ช่วยลดการกําหนดคีย์ด้วยตนเอง ลดความเสี่ยงของข้อผิดพลาดและทําให้การรับข้อมูลง่ายขึ้น ค่าเฉพาะที่จัดการโดยระบบเหมาะอย่างยิ่งสําหรับใช้เป็นคีย์ตัวแทนและคีย์หลัก เมื่อเทียบกับวิธีการ IDENTITY แบบแมนนวล คอลัมน์มีประสิทธิภาพดีกว่าเพราะคีย์ที่ไม่ซ้ํากันถูกสร้างขึ้นโดยอัตโนมัติโดยไม่ต้องมีตรรกะการสืบค้นเพิ่มเติม

ชนิดข้อมูล bigint ที่จําเป็นสําหรับ IDENTITY คอลัมน์ สามารถเก็บค่าจํานวนเต็มบวกได้สูงสุด 9,223,372,036,854,775,807 ค่า ช่วงนี้ช่วยให้แต่ละแถวได้รับค่าที่ไม่ซ้ํากันในคอลัมน์ตลอด IDENTITY อายุการใช้งานของตาราง

สําหรับแผนการโยกย้ายข้อมูลด้วยคีย์ตัวแทนจากแพลตฟอร์มฐานข้อมูลอื่น โปรดดู โยกย้ายคอลัมน์ IDENTITY ไปยัง Fabric คลังข้อมูล

วากยสัมพันธ์

หากต้องการกําหนดIDENTITYคอลัมน์ใน Fabric คลังข้อมูล ให้ใช้คุณสมบัติในIDENTITYคํานิยามคอลัมน์:

CREATE TABLE { warehouse_name.schema_name.table_name | schema_name.table_name | table_name } (
    [ column_name ] BIGINT IDENTITY ,
    [ ,...n ]
    -- Other columns here
);

คอลัมน์เอกลักษณ์ไม่จําเป็นต้องเป็นคอลัมน์แรกในคําจํากัดความของตาราง

วิธีการทํางานของคอลัมน์ IDENTITY

ใน Fabric คลังข้อมูล คุณไม่สามารถระบุค่าเริ่มต้นหรือการเพิ่มค่าที่กําหนดเองได้ ระบบจัดการค่าภายในเพื่อให้มั่นใจในความเป็นเอกลักษณ์ IDENTITY คอลัมน์จะสร้างค่าจํานวนเต็มบวกเสมอ แถวใหม่แต่ละแถวจะได้รับค่าใหม่ และรับประกันความเป็นเอกลักษณ์ตราบเท่าที่ตารางยังมีอยู่ เมื่อใช้ค่า IDENTITY หนึ่งแล้ว จะไม่ใช้ค่านั้นอีก ช่องว่างอาจปรากฏในค่าที่ IDENTITY คอลัมน์สร้างขึ้น

การจัดสรรค่า

เนื่องจากสถาปัตยกรรมแบบกระจายของเอนจินคลังสินค้า คุณสมบัตินี้ IDENTITY จึงไม่รับประกันลําดับการจัดสรรค่าตัวแทน คุณสมบัตินี้ขยายไปยังโหนดคอมพิวเตอร์เพื่อเพิ่มความขนานโดยไม่กระทบต่อประสิทธิภาพการโหลด ดังนั้น ช่วงค่าของงานการนําเข้าต่าง ๆ อาจไม่เรียงลําดับกัน

ตัวอย่างต่อไปนี้แสดงพฤติกรรมนี้:

-- Create a table with an IDENTITY column
CREATE TABLE dbo.Table1(
    Column1 BIGINT IDENTITY,
    Column2 VARCHAR(30) NULL
)

-- Ingestion task A
INSERT INTO dbo.Table1
VALUES (NULL), (NULL), (NULL), (NULL);

-- Ingestion task B
INSERT INTO dbo.Table1
VALUES (NULL), (NULL), (NULL), (NULL);

-- Review the data
SELECT * FROM dbo.Table1;

ผลลัพธ์ตัวอย่าง:

ในตัวอย่าง Ingestion task A นี้ และ Ingestion task B รันแบบลําดับเป็นงานอิสระ แม้ว่างานจะทํางานต่อเนื่องกัน แต่สี่แถวแรกและแถวสุดท้ายจะมีช่วงคีย์ระบุตัวตนที่แตกต่างกันในdbo.Table1.Column1 ช่องว่างระหว่างช่วงที่มอบหมายให้กับงาน A และงาน B ก็อาจเกิดขึ้นได้เช่นกัน

IDENTITYin Fabric คลังข้อมูล รับประกันว่าค่าIDENTITYทั้งหมดในคอลัมน์จะไม่ซ้ํากันตราบใดที่IDENTITY_INSERTไม่ได้ใช้งาน แต่ช่องว่างอาจเกิดขึ้นในช่วงที่สร้างขึ้นสําหรับงานนําเข้าข้อมูล

วัตถุเมตาดาต้าระบบ

วัตถุเมตาดาต้าระบบต่อไปนี้มีให้ใช้งานและมีประโยชน์เมื่อออกแบบและทํางานกับค่าตัวตนใน Fabric คลังข้อมูล

แสดงรายการคอลัมน์ระบุตัวตนด้วยมุมมองระบบ sys.identity_columns

ใช้มุมมองแคตตาล็อก sys.identity_columns เพื่อแสดงรายการคอลัมน์ระบุตัวตนทั้งหมดในคลังสินค้า ตัวอย่างต่อไปนี้แสดงรายการตารางทั้งหมดที่มี IDENTITY คอลัมน์ รวมถึงชื่อสคีมา ตาราง และชื่อคอลัมน์เอกลักษณ์:

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    c.name AS IdentityColumnName
FROM
    sys.identity_columns AS ic
INNER JOIN
    sys.columns AS c ON ic.[object_id] = c.[object_id]
    AND ic.column_id = c.column_id
INNER JOIN
    sys.tables AS t ON ic.[object_id] = t.[object_id]
INNER JOIN
    sys.schemas AS s ON t.[schema_id] = s.[schema_id]
ORDER BY
    s.name, t.name;

ใน Fabric คลังข้อมูล seed_value คอลัมน์ sys.identity_columns และ increment_value จะคืนค่า NULL และจะไม่อัปเดตหลังจากสร้างคอลัมน์ identity แล้ว คอลัมน์จะ last_value คืนค่า NULL โดยค่าเริ่มต้น แต่จะสลับถาวรไป -1 หลังจากการแทรกตัวตนครั้งแรกบนตาราง

แทรกค่าที่มี IDENTITY_INSERT

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

ใช้ SET IDENTITY_INSERT เพื่ออนุญาตการแทรกแบบชัดเจนในคอลัมน์ระบุตัวตนชั่วคราว:

SET IDENTITY_INSERT dbo.DimCustomer ON;

INSERT INTO dbo.DimCustomer (CustomerKey, CustomerName, Email)
VALUES (-1, 'John Doe', 'john@contoso.com');

SET IDENTITY_INSERT dbo.DimCustomer OFF;

เมื่อ IDENTITY_INSERT คือ ON:

• ต้องมีรายการคอลัมน์กับคําสั่งนี้INSERT
• สามารถตั้งค่า IDENTITY_INSERT เป็น ได้ ON เพียงหนึ่งตารางต่อเซสชันเท่านั้น

สําคัญ

หลังจากปิดIDENTITY_INSERTแล้ว ให้เพิ่มค่าตัวตนด้วย DBCC CHECKIDENT

รีซีดค่าตัวตนด้วย DBCC CHECKIDENT

หลังจากที่คุณแทรกค่าที่ระบุชัดเจนด้วย IDENTITY_INSERT, ให้ใช้ DBCC CHECKIDENT เพื่อเติมคอลัมน์เอกลักษณ์ใหม่ การดําเนินการนี้จะ RESEED สแกนช่วงข้อมูลระบุตัวตนที่ใช้และสํารองทั้งหมดทั่วโหนดคํานวณแบบกระจายเพื่อกําหนดค่าถัดไปที่ถูกต้อง เพื่อให้มั่นใจในความเป็นเอกลักษณ์และป้องกันการชนกันของกุญแจ

DBCC CHECKIDENT('dbo.DimProduct', RESEED);

ใน Fabric คลังข้อมูล DBCC CHECKIDENT รองรับเฉพาะตัวเลือกเท่านั้นRESEED คลังข้อมูลจะกําหนดช่วงค่าถัดไปที่ถูกต้องโดยอัตโนมัติ และคุณไม่สามารถระบุค่า reseed แบบกําหนดเองได้ สําหรับข้อมูลเพิ่มเติม โปรดดูที่ DBCC CHECKIDENT

Limitations

สําหรับข้อมูลเพิ่มเติม ดูที่ คอลัมน์ IDENTITY,IDENTITY (Transact-SQL) และ Create tables in the Warehouse in Microsoft Fabric

• เฉพาะชนิดข้อมูล bigint เท่านั้นที่ได้รับการสนับสนุนสําหรับ IDENTITY คอลัมน์ใน Fabric คลังข้อมูล ข้อมูลประเภทอื่นทําให้เกิดข้อผิดพลาด
• การกําหนดเมล็ดพันธุ์และการเพิ่มไม่ได้รับการสนับสนุน ระบบจัดการค่าภายใน
• IDENTITYการเพิ่มคอลัมน์ลงในตารางที่มีอยู่แล้วไม่ALTER TABLEรองรับ พิจารณาใช้ CREATE TABLE AS SELECT (CTAS) หรือ SELECT... INTO เพื่อสร้างสําเนาของตารางที่มีอยู่และเพิ่ม IDENTITY คอลัมน์
• ข้อจํากัดมีผลกับวิธีการ IDENTITY เก็บรักษาคอลัมน์เมื่อคุณสร้างตารางโดยเลือกจากตารางอื่นที่มี CTAS หรือ SELECT...INTO. สําหรับข้อมูลเพิ่มเติม โปรดดูส่วนประเภทข้อมูลของ SELECT - INTO Clause (Transact-SQL)
• DBCC CHECKIDENTรองรับเฉพาะตัวเลือกเท่านั้นRESEED ไม่รองรับการระบุค่า reseed แบบกําหนดเองหรือการใช้NORESEED
• IDENTITY คอลัมน์จะสร้างค่าที่รับประกันว่าไม่ซ้ํากัน แต่ค่าเหล่านั้นไม่จําเป็นต้องเรียงลําดับหรือเรียงลําดับ และอาจมีช่องว่างเกิดขึ้นได้

ตัวอย่าง

ตอบ สร้างตารางด้วยคอลัมน์ IDENTITY

CREATE TABLE Employees (
    EmployeeID BIGINT IDENTITY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

คําสั่งนี้จะสร้างEmployeesตารางที่ทุกแถวใหม่จะได้รับค่า bigint ที่ไม่ซ้ํากันEmployeeIDโดยอัตโนมัติ

B. แทรกแถวลงในตารางที่มีคอลัมน์ระบุตัวตน

เมื่อคุณกําหนดค่าสําหรับทุกคอลัมน์ที่ไม่ใช่ตัวตนตามลําดับที่กําหนด คุณไม่จําเป็นต้องระบุรายการคอลัมน์:

INSERT INTO Employees VALUES ('Quarantino', 'Esposito');

คุณยังสามารถกําหนดรายการคอลัมน์ที่ละเว้นคอลัมน์เอกลักษณ์ได้เช่นกัน:

INSERT INTO Employees (FirstName, LastName)
VALUES ('Ensi', 'Vasala');

C. แทรกค่าที่ชัดเจนด้วย IDENTITY_INSERT

SET IDENTITY_INSERT dbo.Employees ON;

INSERT INTO dbo.Employees (EmployeeID, FirstName, LastName)
VALUES (100, 'Sentinel', 'Row');

SET IDENTITY_INSERT dbo.Employees OFF;

D. แทรกค่าที่ระบุชัดเจนพร้อม COPY INTO

คําสั่งนี้ COPY INTO รองรับ IDENTITY_INSERT ตัวเลือกในการนําเข้าค่าที่ชัดเจนภายในคําสั่ง COPY INTO ตัวเลือกจะแทนที่การตั้งค่าระดับเซสชันใด ๆ สําหรับ IDENTITY_INSERT.

COPY INTO dbo.Employees (EmployeeID 1, FirstName 2, LastName 3)
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV',
    IDENTITY_INSERT = 'ON'
);

E. รีซีตตารางหลังจากแทรกแบบชัดเจน

DBCC CHECKIDENT('dbo.Employees', RESEED);

F. สร้างตารางด้วย CREATE TABLE AS SELECT

ใช้ CTAS เพื่อสร้างสําเนาของตารางและเก็บคุณสมบัติไว้ใน IDENTITY ตารางเป้าหมาย:

CREATE TABLE RetiredEmployees
AS SELECT * FROM Employees;

คอลัมน์ในตารางเป้าหมายจะสืบทอดคุณสมบัตินี้ IDENTITY มาจากตารางต้นทาง สําหรับข้อจํากัด โปรดดูส่วนประเภทข้อมูลของ SELECT - INTO Clause

G. สร้างตารางด้วย SELECT... เข้าไป

ใช้เพื่อสร้าง SELECT...INTO สําเนาของตารางและคงคุณสมบัติ IDENTITY ไว้ในตารางเป้าหมาย:

SELECT *
INTO dbo.RetiredEmployees
FROM dbo.Employees
WHERE LastName = 'Esposito';

คอลัมน์ในตารางเป้าหมายจะสืบทอดคุณสมบัตินี้ IDENTITY มาจากตารางต้นทาง สําหรับข้อจํากัด โปรดดูส่วนประเภทข้อมูลของ SELECT - INTO Clause

ขั้นตอนถัดไป

ใช้คอลัมน์ IDENTITY ใน Fabric คลังข้อมูล

เนื้อหาที่เกี่ยวข้อง

• ชุด IDENTITY_INSERT (Transact-SQL)
• DBCC CHECKIDENT (Transact-SQL)
• สร้างตารางในคลังสินค้า
• โยกย้ายคอลัมน์ IDENTITY ไปยัง Fabric คลังข้อมูล