إنشاء جدول كما هو محدد (CTAS)

تشرح هذه المقالة عبارة CREATE TABLE AS SELECT (CTAS) T-SQL في تجمع SQL المخصص (المعروف سابقًا باسم SQL DW) لتطوير الحلول. توفر المقالة أيضًا أمثلة على التعليمات البرمجية.

CREATE TABLE AS SELECT

عبارة CREATE TABLE AS SELECT (CTAS) هي واحدة من أهم ميزات T-SQL المتوفرة. تمثل CTAS عملية متوازية تنشئ جدولًا جديدًا استنادًا إلى إخراج عبارة SELECT. تعد CTAS أبسط وأسرع طريقة لإنشاء البيانات وإدراجها في جدول باستخدام أمر واحد.

حدد... INTO مقابل CTAS

يمثل CTAS أكثر إصدار قابل للتخصيص من عبارة SELECT...INTO.

فيما يلي مثال على SELECT... INTO بسيط:

SELECT *
INTO    [dbo].[FactInternetSales_new]
FROM    [dbo].[FactInternetSales]

لا يسمح خيار «حدد... إلى» بتغيير إما أسلوب التوزيع أو نوع الفهرس كجزء من العملية. إنشاء [dbo].[FactInternetSales_new]جدول جديد باستخدام نوع التوزيع الافتراضي للترتيب الدوري، وبنية الجدول الافتراضي من مؤشر تخزين الأعمدة متفاوت المسافات.

وباستخدام CTAS- من ناحية أخرى- يمكنك تحديد كل من توزيع بيانات الجدول بالإضافة إلى نوع بنية الجدول. لتحويل المثال السابق إلى CTAS:

CREATE TABLE [dbo].[FactInternetSales_new]
WITH
(
 DISTRIBUTION = ROUND_ROBIN
 ,CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT  *
FROM    [dbo].[FactInternetSales];

إشعار

إذا كنت تحاول تغيير الفهرس في عملية CTAS فحسب، وتم توزيع تجزئة الجدول المصدر، فاحتفظ بنفس عمود التوزيع ونوع البيانات. هذا يتجنب نقل البيانات عبر التوزيع أثناء العملية، وهو أكثر كفاءة.

استخدام CTAS لنسخ جدول

ربما يكون أحد الاستخدامات الأكثر شيوعًا لـ CTAS هو إنشاء نسخة من جدول من أجل تغيير DDL. لنفترض أنك أنشأت الجدول في الأصل ك ROUND_ROBIN، وتريد الآن تغييره إلى جدول موزع على عمود. CTAS هو كيف يمكنك تغيير عمود التوزيع. يمكنك أيضًا استخدام CTAS لتغيير أنواع التقسيم أو الفهرسة أو الأعمدة.

لنفترض أنك أنشأت هذا الجدول بتحديد HEAP واستخدام نوع التوزيع الافتراضي ROUND_ROBIN.

CREATE TABLE FactInternetSales
(
    ProductKey int NOT NULL,
    OrderDateKey int NOT NULL,
    DueDateKey int NOT NULL,
    ShipDateKey int NOT NULL,
    CustomerKey int NOT NULL,
    PromotionKey int NOT NULL,
    CurrencyKey int NOT NULL,
    SalesTerritoryKey int NOT NULL,
    SalesOrderNumber nvarchar(20) NOT NULL,
    SalesOrderLineNumber tinyint NOT NULL,
    RevisionNumber tinyint NOT NULL,
    OrderQuantity smallint NOT NULL,
    UnitPrice money NOT NULL,
    ExtendedAmount money NOT NULL,
    UnitPriceDiscountPct float NOT NULL,
    DiscountAmount float NOT NULL,
    ProductStandardCost money NOT NULL,
    TotalProductCost money NOT NULL,
    SalesAmount money NOT NULL,
    TaxAmt money NOT NULL,
    Freight money NOT NULL,
    CarrierTrackingNumber nvarchar(25),
    CustomerPONumber nvarchar(25)
)
WITH( 
 HEAP, 
 DISTRIBUTION = ROUND_ROBIN 
);

الآن تريد إنشاء نسخة جديدة من هذا الجدول، مع Clustered Columnstore Index، حتى تتمكن من الاستفادة من أداء جداول تخزين الأعمدة المجمعة. تريد أيضا توزيع هذا الجدول على ProductKey، لأنك تتوقع الربط على هذا العمود وتريد تجنب حركة البيانات أثناء الربط على ProductKey. وأخيرًا، تريد أيضًا إضافة تقسيم على OrderDateKey، حتى يمكنك حذف البيانات القديمة بسرعة عن طريق إسقاط الأقسام القديمة. فيما يلي عبارة CTAS، التي تنسخ الجدول القديم إلى جدول جديد.

CREATE TABLE FactInternetSales_new
WITH
(
    CLUSTERED COLUMNSTORE INDEX,
    DISTRIBUTION = HASH(ProductKey),
    PARTITION
    (
        OrderDateKey RANGE RIGHT FOR VALUES
        (
        20000101,20010101,20020101,20030101,20040101,20050101,20060101,20070101,20080101,20090101,
        20100101,20110101,20120101,20130101,20140101,20150101,20160101,20170101,20180101,20190101,
        20200101,20210101,20220101,20230101,20240101,20250101,20260101,20270101,20280101,20290101
        )
    )
)
AS SELECT * FROM FactInternetSales;

أخيرًا، يمكنك إعادة تسمية الجداول، للتبديل في الجدول الجديد ثم قطع الجدول القديم.

RENAME OBJECT FactInternetSales TO FactInternetSales_old;
RENAME OBJECT FactInternetSales_new TO FactInternetSales;

DROP TABLE FactInternetSales_old;

نوع بيانات الحالة صراحة وإمكانية قبول القيم الفارغة

عند ترحيل التعليمات البرمجية، قد تجد أنك تعمل عبر هذا النوع من نمط الترميز:

DECLARE @d decimal(7,2) = 85.455
,       @f float(24)    = 85.455

CREATE TABLE result
(result DECIMAL(7,2) NOT NULL
)
WITH (DISTRIBUTION = ROUND_ROBIN)

INSERT INTO result
SELECT @d*@f;

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

لا ينتج عن التعليمات البرمجية التالية نفس النتيجة:

DECLARE @d decimal(7,2) = 85.455
, @f float(24)    = 85.455;

CREATE TABLE ctas_r
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT @d*@f as result;

لاحظ أن عمود "النتيجة" يحمل نوع البيانات وقيم إمكانية قبول القيم الفارغة للتعبير. يمكن أن يؤدي نقل نوع البيانات إلى الأمام إلى تباينات دقيقة في القيم إذا لم تكن حذرًا.

جرب هذا المثال:

SELECT result,result*@d
from result;

SELECT result,result*@d
from ctas_r;

تختلف القيمة المخزنة للنتيجة. نظرًا لاستخدام القيمة الدائمة في عمود النتيجة في تعبيرات أخرى، يصبح الخطأ أكثر أهمية.

هذا مهم لترحيل البيانات. على الرغم من أن الاستعلام الثاني أكثر دقة، إلا أن هناك مشكلة. ستكون البيانات مختلفة مقارنة بالنظام المصدر، هذا يؤدي إلى أسئلة تتعلق بتكامل البيانات في الترحيل. هذه واحدة من تلك الحالات النادرة التي تكون فيها الإجابة "الخاطئة" هي في الواقع الإجابة الصحيحة!

السبب في أننا نرى تفاوتًا بين النتيجتين يرجع إلى تحويل النوع الضمني. في المثال الأول، يعرف الجدول تعريف العمود. عند إدراج الصف، يحدث تحويل نوع ضمني. في المثال الثاني، لا يوجد تحويل نوع ضمني لأن التعبير يعرف نوع بيانات العمود.

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

لحل هذه المشكلات، يجب تعيين تحويل النوع وقابلية القيم الخالية بشكل صريح في جزء SELECT من عبارة CTAS. لا يمكنك تعيين هذه الخصائص في 'CREATE TABLE'. يوضح المثال التالي طريقة إصلاح التعليمة البرمجية:

DECLARE @d decimal(7,2) = 85.455
, @f float(24)    = 85.455

CREATE TABLE ctas_r
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT ISNULL(CAST(@d*@f AS DECIMAL(7,2)),0) as result

لاحظ ما يلي:

• يمكنك استخدام CAST أو CONVERT.
• استخدم ISNULL، ليس COALESCE، لفرض NULLability. انظر ما يلي:
• ISNULL هي الدالة الخارجية.
• الجزء الثاني من ISNULL هو ثابت، 0.

إشعار

لكي يتم تعيين قابلية القيم الخالية بشكل صحيح، من الضروري استخدام ISNULL ليس COALESCE. الدالة COALESCE ليست دالة حتمية، وبالتالي ستكون نتيجة التعبير دائمًا NULLable. ISNULL مختلف. إنه أمر محدد. لذلك، عندما يكون الجزء الثاني من الدالة ISNULL ثابتًا أو حرفيًا، ستكون القيمة الناتجة NOT NULL.

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

CREATE TABLE [dbo].[Sales]
(
    [date]      INT     NOT NULL
, [product]   INT     NOT NULL
, [store]     INT     NOT NULL
, [quantity]  INT     NOT NULL
, [price]     MONEY   NOT NULL
, [amount]    MONEY   NOT NULL
)
WITH
(   DISTRIBUTION = HASH([product])
,   PARTITION   (   [date] RANGE RIGHT FOR VALUES
                    (20000101,20010101,20020101
                    ,20030101,20040101,20050101
                    )
                )
);

مع ذلك، حقل المقدار هو تعبير محسوب. إنه ليس جزءًا من البيانات المصدر.

لإنشاء مجموعة البيانات المقسمة، قد تحتاج إلى استخدام التعليمات البرمجية التالية:

CREATE TABLE [dbo].[Sales_in]
WITH
( DISTRIBUTION = HASH([product])
, PARTITION   (   [date] RANGE RIGHT FOR VALUES
                    (20000101,20010101
                    )
                )
)
AS
SELECT
    [date]
,   [product]
,   [store]
,   [quantity]
,   [price]
,   [quantity]*[price]  AS [amount]
FROM [stg].[source]
OPTION (LABEL = 'CTAS : Partition IN table : Create');

سيتم تشغيل الاستعلام بشكل جيد تمامًا. تأتي المشكلة عندما تحاول إجراء تبديل القسم. تعريفات الجدول غير متطابقة. لجعل تعريفات الجدول متطابقة، قم بتعديل CTAS لإضافة دالة ISNULL للحفاظ على سمة القيمة الخالية للعمود.

CREATE TABLE [dbo].[Sales_in]
WITH
( DISTRIBUTION = HASH([product])
, PARTITION   (   [date] RANGE RIGHT FOR VALUES
                    (20000101,20010101
                    )
                )
)
AS
SELECT
  [date]
, [product]
, [store]
, [quantity]
, [price]
, ISNULL(CAST([quantity]*[price] AS MONEY),0) AS [amount]
FROM [stg].[source]
OPTION (LABEL = 'CTAS : Partition IN table : Create');

يمكنك أن ترى أن تناسق النوع والحفاظ على خصائص إمكانية قبول القيم الفارغة على CTAS هو أفضل ممارسة هندسية. يساعد على الحفاظ على التكامل في حساباتك، ويضمن أيضًا إمكانية تبديل القسم.

CTAS هي واحدة من أهم العبارات في لغة الاستعلامات المركبة Synapse . تأكد من فهمك لها جيدًا. راجع وثائق CTAS.

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

لمزيد من نصائح التطوير، راجع نظرة عامة على التطوير.