الجداول المؤقتة في Synapse SQL

تحتوي هذه المقالة على إرشادات أساسية لاستخدام الجداول المؤقتة وتسلط الضوء على مبادئ الجداول المؤقتة على مستوى الجلسة داخل Synapse SQL.

يمكن لكل من تجمع SQL المخصص وموارد تجمع SQL بلا خادم الاستفادة من الجداول المؤقتة. يحتوي تجمع SQL بلا خادم على قيود تمت مناقشتها في نهاية هذه المقالة.

جداول مؤقتة

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

الجداول المؤقتة في تجمع SQL المخصص

في مورد تجمع SQL المخصص، توفر الجداول المؤقتة ميزة أداء لأن نتائجها مكتوبة إلى التخزين المحلي بدلاً من التخزين عن بعد.

إنشاء جدول مؤقت «Temporary table»

يتم إنشاء الجداول المؤقتة عن طريق إضافة البادئة # إلى اسم الجدول. على سبيل المثال:

CREATE TABLE #stats_ddl
(
    [schema_name]        NVARCHAR(128) NOT NULL
,    [table_name]            NVARCHAR(128) NOT NULL
,    [stats_name]            NVARCHAR(128) NOT NULL
,    [stats_is_filtered]     BIT           NOT NULL
,    [seq_nmbr]              BIGINT        NOT NULL
,    [two_part_name]         NVARCHAR(260) NOT NULL
,    [three_part_name]       NVARCHAR(400) NOT NULL
)
WITH
(
    DISTRIBUTION = HASH([seq_nmbr])
,    HEAP
)

يمكن أيضًا إنشاء الجداول المؤقتة من خلال CTAS باستخدام نفس النهج بالضبط:

CREATE TABLE #stats_ddl
WITH
(
    DISTRIBUTION = HASH([seq_nmbr])
,    HEAP
)
AS
(
SELECT
        sm.[name]                                                                AS [schema_name]
,        tb.[name]                                                                AS [table_name]
,        st.[name]                                                                AS [stats_name]
,        st.[has_filter]                                                            AS [stats_is_filtered]
,       ROW_NUMBER()
        OVER(ORDER BY (SELECT NULL))                                            AS [seq_nmbr]
,                                 QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [two_part_name]
,        QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [three_part_name]
FROM    sys.objects            AS ob
JOIN    sys.stats            AS st    ON    ob.[object_id]        = st.[object_id]
JOIN    sys.stats_columns    AS sc    ON    st.[stats_id]        = sc.[stats_id]
                                    AND st.[object_id]        = sc.[object_id]
JOIN    sys.columns            AS co    ON    sc.[column_id]        = co.[column_id]
                                    AND    sc.[object_id]        = co.[object_id]
JOIN    sys.tables            AS tb    ON    co.[object_id]        = tb.[object_id]
JOIN    sys.schemas            AS sm    ON    tb.[schema_id]        = sm.[schema_id]
WHERE    1=1
AND        st.[user_created]   = 1
GROUP BY
        sm.[name]
,        tb.[name]
,        st.[name]
,        st.[filter_definition]
,        st.[has_filter]
)
;

إنهاء الجداول المؤقتة

عند إنشاء جلسة جديدة، يجب ألا تتوجد أي جداول مؤقتة. ومع ذلك، إذا كنت تستدعي نفس الإجراء المخزن الذي ينشئ جدولاً مؤقتًا بنفس الاسم، للتأكد من نجاح عبارات CREATE TABLE، فاستخدم فحصًا بسيطًا قبل التواجد من خلال DROP:

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
    DROP TABLE #stats_ddl
END

لاتساق كتابة التعليمة البرمجية، من الممارسات الجيدة استخدام هذا النمط لكل من الجداول والجداول المؤقتة. من الجيد أيضًا استخدام DROP TABLE لإزالة الجداول المؤقتة عند الانتهاء منها.

في تطوير الإجراء المخزن، من الشائع رؤية أوامر الإفلات مجمعة معاً في نهاية الإجراء لضمان تنظيف هذه العناصر.

DROP TABLE #stats_ddl

نمذجة التعليمة البرمجية

يمكن استخدام الجداول المؤقتة في أي مكان في جلسة عمل المستخدم. يمكن بعد ذلك استغلال هذه الإمكانية لمساعدتك في وضع معيار للتعليمات البرمجية للتطبيق الخاص بك. للتوضيح، يقوم الإجراء المخزن التالي بإنشاء DDL لتحديث كل الإحصائيات في قاعدة البيانات حسب الاسم الإحصائي:

CREATE PROCEDURE    [dbo].[prc_sqldw_update_stats]
(   @update_type    tinyint -- 1 default 2 fullscan 3 sample 4 resample
    ,@sample_pct     tinyint
)
AS

IF @update_type NOT IN (1,2,3,4)
BEGIN;
    THROW 151000,'Invalid value for @update_type parameter. Valid range 1 (default), 2 (fullscan), 3 (sample) or 4 (resample).',1;
END;

IF @sample_pct IS NULL
BEGIN;
    SET @sample_pct = 20;
END;

IF OBJECT_ID('tempdb..#stats_ddl') IS NOT NULL
BEGIN
    DROP TABLE #stats_ddl
END

CREATE TABLE #stats_ddl
WITH
(
    DISTRIBUTION = HASH([seq_nmbr])
)
AS
(
SELECT
        sm.[name]                                                                AS [schema_name]
,        tb.[name]                                                                AS [table_name]
,        st.[name]                                                                AS [stats_name]
,        st.[has_filter]                                                            AS [stats_is_filtered]
,       ROW_NUMBER()
        OVER(ORDER BY (SELECT NULL))                                            AS [seq_nmbr]
,                                 QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [two_part_name]
,        QUOTENAME(DB_NAME())+'.'+QUOTENAME(sm.[name])+'.'+QUOTENAME(tb.[name])  AS [three_part_name]
FROM    sys.objects            AS ob
JOIN    sys.stats            AS st    ON    ob.[object_id]        = st.[object_id]
JOIN    sys.stats_columns    AS sc    ON    st.[stats_id]        = sc.[stats_id]
                                    AND st.[object_id]        = sc.[object_id]
JOIN    sys.columns            AS co    ON    sc.[column_id]        = co.[column_id]
                                    AND    sc.[object_id]        = co.[object_id]
JOIN    sys.tables            AS tb    ON    co.[object_id]        = tb.[object_id]
JOIN    sys.schemas            AS sm    ON    tb.[schema_id]        = sm.[schema_id]
WHERE    1=1
AND        st.[user_created]   = 1
GROUP BY
        sm.[name]
,        tb.[name]
,        st.[name]
,        st.[filter_definition]
,        st.[has_filter]
)
SELECT
    CASE @update_type
    WHEN 1
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+');'
    WHEN 2
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH FULLSCAN;'
    WHEN 3
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH SAMPLE '+CAST(@sample_pct AS VARCHAR(20))+' PERCENT;'
    WHEN 4
    THEN 'UPDATE STATISTICS '+[two_part_name]+'('+[stats_name]+') WITH RESAMPLE;'
    END AS [update_stats_ddl]
,   [seq_nmbr]
FROM    #stats_ddl
;
GO

في هذه المرحلة، الإجراء الوحيد الذي حدث هو إنشاء إجراء مخزن ينشئ الجدول المؤقت #stats_ddl. يتم إنهاء الإجراء المخزن #stats_ddl إذا كان موجودًا بالفعل. يضمن هذا الإنهاء عدم فشله إذا تم تشغيله أكثر من مرة واحدة في جلسة العمل.

نظرًا إلى عدم وجود DROP TABLE في نهاية الإجراء المخزن، عند اكتمال الإجراء المخزن، يبقى الجدول الذي تم إنشاؤه ويمكن قراءته خارج الإجراء المخزن.

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

EXEC [dbo].[prc_sqldw_update_stats] @update_type = 1, @sample_pct = NULL;

DECLARE @i INT              = 1
,       @t INT              = (SELECT COUNT(*) FROM #stats_ddl)
,       @s NVARCHAR(4000)   = N''

WHILE @i <= @t
BEGIN
    SET @s=(SELECT update_stats_ddl FROM #stats_ddl WHERE seq_nmbr = @i);

    PRINT @s
    EXEC sp_executesql @s
    SET @i+=1;
END

DROP TABLE #stats_ddl;

قيود الجدول المؤقتة

يحتوي تجمع SQL المخصص على بعض قيود التنفيذ للجداول المؤقتة:

• يتم اعتماد الجداول المؤقتة المشمولة في نطاق جلسة العمل فقط. الجداول المؤقتة العامة غير مدعومة.
• لا يمكن إنشاء طرق العرض باستخدام الجداول المؤقتة.
• لا يمكن إنشاء الجداول المؤقتة إلا باستخدام التجزئة أو التوزيع الدائري. لا يتم دعم توزيع الجدول المؤقت المنسوخ.

الجداول المؤقتة في تجمع SQL بلا خادم

الجداول المؤقتة في تجمع SQL بلا خادم مدعومة ولكن استخدامها محدود. لا يمكن استخدامها في الاستعلامات التي تستهدف الملفات.

على سبيل المثال، لا يمكنك ربط جدول مؤقت ببيانات من ملفات في التخزين. يقتصر عدد الجداول المؤقتة على 100 جدول، ويقتصر حجمها الإجمالي على 100 ميغابايت.

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

لمعرفة المزيد حول تطوير الجداول، راجع المقالة تصميم الجداول باستخدام موارد Synapse SQL.