مشاركة عبر

استخدام IDENTITY لإنشاء مفاتيح بديلة باستخدام تجمع SQL مخصص في Azure Synapse Analytics

  • مقالة

في هذه المقالة، ستجد توصيات وأمثلة لاستخدام خاصية IDENTITY لإنشاء مفاتيح بديلة على الجداول في مجموعة SQL المخصصة.

ما هو مفتاح بديل

المفتاح البديل في الجدول هو عمود بمعرف فريد لكل صف. لا يتم إنشاء المفتاح من بيانات الجدول. يحب مصممي البيانات إنشاء مفاتيح بديلة على جداولهم عند تصميم نماذج مستودع البيانات. يمكنك استخدام خاصية IDENTITY لتحقيق هذا الهدف ببساطة وفعالية دون التأثير على أداء التحميل.

ملاحظة

في Azure Synapse Analytics:

  • تزيد قيمة IDENTITY من تلقاء نفسها في كل توزيع ولا تتداخل مع قيم IDENTITY في التوزيعات الأخرى. لا يمكن ضمان أن تكون قيمة IDENTITY في Synapse فريدة إذا قام المستخدم صراحة بإدراج قيمة مكررة مع "SET IDENTITY_INSERT ON" أو إعادة إرسال IDENTITY. للحصول على تفاصيل، راجع CREATE TABLE (Transact-SQL) IDENTITY (Property).
  • لا يضمن UPDATE على عمود التوزيع قيمة IDENTITY لتكون فريدة. استخدم DBCC CHECKIDENT (Transact-SQL) بعد UPDATE على عمود التوزيع للتحقق من التفرد.

إنشاء جدول بعمود IDENTITY

تم تصميم خاصية IDENTITY لتوسيع نطاق التوزيعات عبر جميع التوزيعات في تجمع SQL المخصص دون التأثير على أداء التحميل. لذلك، فإن تنفيذ IDENTITY موجه نحو تحقيق هذه الأهداف.

يمكنك تعريف جدول على أنه يحتوي على خاصية IDENTITY عند إنشاء الجدول لأول مرة باستخدام بناء جملة مشابه للعبارة التالية:

CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1) NOT NULL
,    C2 INT NULL
)
WITH
(   DISTRIBUTION = HASH(C2)
,   CLUSTERED COLUMNSTORE INDEX
)
;

يمكنك بعد ذلك استخدام INSERT..SELECT لملء الجدول.

يسلط هذا الجزء المتبقي من هذا القسم الضوء على الفروق الدقيقة في التنفيذ لمساعدتك على فهمها بشكل كامل.

تخصيص القيم

لا تضمن خاصية IDENTITY الترتيب الذي يتم فيه تخصيص القيم البديلة بسبب البنية الموزعة لمستودع البيانات. تم تصميم خاصية IDENTITY لتوسيع نطاق التوزيعات عبر جميع التوزيعات في تجمع SQL المخصص دون التأثير على أداء التحميل.

المثال التالي توضيح:

CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1)    NOT NULL
,    C2 VARCHAR(30)                NULL
)
WITH
(   DISTRIBUTION = HASH(C2)
,   CLUSTERED COLUMNSTORE INDEX
)
;

INSERT INTO dbo.T1
VALUES (NULL);

INSERT INTO dbo.T1
VALUES (NULL);

SELECT *
FROM dbo.T1;

DBCC PDW_SHOWSPACEUSED('dbo.T1');

في المثال السابق، هبط صفان في التوزيع 1. يحتوي الصف الأول على القيمة البديلة 1 في العمود C1، بينما يحتوي الصف الثاني على القيمة البديلة 61. تم إنشاء هاتين القيمتين بواسطة خاصية IDENTITY. ومع ذلك، فإن تخصيص القيم ليس متجاوراً. هذا السلوك يكون حسب التصميم.

بيانات منحرفة

يتم توزيع نطاق القيم لنوع البيانات بالتساوي عبر التوزيعات. إذا كان الجدول الموزَّع يعاني من بيانات منحرفة، فيمكن استنفاد نطاق القيم المتاحة لنوع البيانات قبل الأوان. على سبيل المثال، إذا انتهى الأمر بجميع البيانات في توزيع واحد، فعندئذٍ يكون للجدول حق الوصول إلى واحد على ستين فقط من قيم نوع البيانات. لهذا السبب، تقتصر خاصية IDENTITY على أنواع البيانات INT وBIGINT فقط.

SELECT..INTO

عند تحديد عمود IDENTITY موجود في جدول جديد، يرث العمود الجديد خاصية IDENTITY، ما لم يكن أحد الشروط التالية صحيحاً:

  • تحتوي عبارة SELECT على صلة.
  • يتم ربط جمل SELECT المتعددة باستخدام UNION.
  • تم إدراج عمود IDENTITY أكثر من مرة في قائمة SELECT.
  • يعد عمود IDENTITY جزءاً من تعبير.

إذا تحقق أي من هذه الشروط، يتم إنشاء العمود "ليس فارغاً" بدلاً من وراثة خاصية IDENTITY.

إنشاء جدول حسب التحديد

يتبع CREATE TABLE AS SELECT (CTAS) نفس سلوك SQL Server الموثق لـ SELECT..INTO. ومع ذلك، لا يمكنك تحديد خاصية IDENTITY في تعريف العمود لجزء CREATE TABLE من العبارة. لا يمكنك أيضاً استخدام وظيفة IDENTITY في الجزء SELECT من CTAS. لتعبئة جدول، تحتاج إلى استخدام CREATE TABLE لتعريف الجدول متبوعاً بـ INSERT..SELECT لتعبئته.

إدراج القيم بشكل صريح في عمود IDENTITY

يدعم تجمع SQL المخصص بناء الجملة SET IDENTITY_INSERT <your table> ON|OFF. يمكنك استخدام بناء الجملة هذا لإدراج القيم بشكل صريح في عمود IDENTITY.

يحب العديد من مصممي البيانات استخدام القيم السالبة المحددة مسبقاً لصفوف معينة في أبعادها. مثال على ذلك هو الصف -1 أو "عضو غير معروف".

يوضح البرنامج النصي التالي كيفية إضافة هذا الصف بشكل صريح باستخدام SET IDENTITY_INSERT:

SET IDENTITY_INSERT dbo.T1 ON;

INSERT INTO dbo.T1
(   C1
,   C2
)
VALUES (-1,'UNKNOWN')
;

SET IDENTITY_INSERT dbo.T1 OFF;

SELECT     *
FROM    dbo.T1
;

تحميل بيانات

إن وجود خاصية IDENTITY له بعض الآثار المترتبة على كود تحميل البيانات. يسلط هذا القسم الضوء على بعض الأنماط الأساسية لتحميل البيانات في جداول باستخدام IDENTITY.

لتحميل البيانات في جدول وإنشاء مفتاح بديل باستخدام IDENTITY، قم بإنشاء الجدول ثم استخدم INSERT..SELECT أو INSERT..VALUES لإجراء التحميل.

يبرز المثال التالي النمط الأساسي:

--CREATE TABLE with IDENTITY
CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1)
,    C2 VARCHAR(30)
)
WITH
(   DISTRIBUTION = HASH(C2)
,   CLUSTERED COLUMNSTORE INDEX
)
;

--Use INSERT..SELECT to populate the table from an external table
INSERT INTO dbo.T1
(C2)
SELECT     C2
FROM    ext.T1
;

SELECT *
FROM   dbo.T1
;

DBCC PDW_SHOWSPACEUSED('dbo.T1');

ملاحظة

لا يمكن استخدام CREATE TABLE AS SELECT حالياً عند تحميل البيانات في جدول يحتوي على عمود IDENTITY.

لمزيد من المعلومات حول تحميل البيانات، راجع تصميم الاستخراج والتحميل والتحويل (ELT) لتجمع SQL المخصص وتحميل أفضل الممارسات.

طرق عرض النظام

يمكنك استخدام طريقة عرض الكتالوج sys.identity_columns لتحديد عمود يحتوي على خاصية IDENTITY.

لمساعدتك على فهم مخطط قاعدة البيانات بشكل أفضل، يوضح هذا المثال كيفية دمج sys.identity_column` مع عروض كتالوج النظام الأخرى:

SELECT  sm.name
,       tb.name
,       co.name
,       CASE WHEN ic.column_id IS NOT NULL
             THEN 1
        ELSE 0
        END AS is_identity
FROM        sys.schemas AS sm
JOIN        sys.tables  AS tb           ON  sm.schema_id = tb.schema_id
JOIN        sys.columns AS co           ON  tb.object_id = co.object_id
LEFT JOIN   sys.identity_columns AS ic  ON  co.object_id = ic.object_id
                                        AND co.column_id = ic.column_id
WHERE   sm.name = 'dbo'
AND     tb.name = 'T1'
;

التقييدات

لا يمكن استخدام الخاصية IDENTITY:

  • عندما يكون نوع بيانات العمود ليس INT أو BIGINT
  • عندما يكون العمود هو أيضاً مفتاح التوزيع
  • عندما يكون الجدول جدولاً خارجياً

الوظائف التالية ذات الصلة غير مدعومة في تجمع SQL المخصص:

المهام المشتركة

يوفر هذا القسم بعض نماذج التعليمة البرمجية التي يمكنك استخدامها لأداء المهام الشائعة عند العمل مع أعمدة IDENTITY.

العمود C1 هو IDENTITY في جميع المهام التالية.

ابحث عن أعلى قيمة مخصصة للجدول

استخدم الدالة MAX() لتحديد أعلى قيمة مخصصة لجدول موزع:

SELECT MAX(C1)
FROM dbo.T1

ابحث عن الأصل والزيادة في خاصية IDENTITY

يمكنك استخدام عروض الكتالوج لاكتشاف زيادة الهوية وقيم التكوين الأولية للجدول باستخدام الاستعلام التالي:

SELECT  sm.name
,       tb.name
,       co.name
,       ic.seed_value
,       ic.increment_value
FROM        sys.schemas AS sm
JOIN        sys.tables  AS tb           ON  sm.schema_id = tb.schema_id
JOIN        sys.columns AS co           ON  tb.object_id = co.object_id
JOIN        sys.identity_columns AS ic  ON  co.object_id = ic.object_id
                                        AND co.column_id = ic.column_id
WHERE   sm.name = 'dbo'
AND     tb.name = 'T1'
;

الخطوات التالية