مشاركة عبر

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

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

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

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

إشعار

في Azure Synapse Analytics:

  • تزيد قيمة IDENTITY من تلقاء نفسها في كل توزيع ولا تتداخل مع قيم IDENTITY في التوزيعات الأخرى. لا تضمن قيمة IDENTITY في Synapse أن تكون فريدة إذا قام المستخدم بإدراج قيمة مكررة بشكل صريح مع SET IDENTITY_INSERT ON IDENTITY أو إعادة تحديده. للحصول على تفاصيل، راجع CREATE TABLE (Transact-SQL) IDENTITY (Property).
  • لا يضمن تحديث عمود التوزيع أن تكون قيمة 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 على INTBIGINT وأنواع البيانات فقط.

SELECT..INTO

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

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

إذا كان أي من هذه الشروط صحيحا، يتم إنشاء NOT NULL العمود بدلا من وراثة الخاصية IDENTITY .

CREATE TABLE AS SELECT

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_columns مع طرق عرض كتالوج النظام الأخرى:

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'
;

المحتوى ذو الصلة

  • Last updated on