محتویات سایت
        برچسب های محبوب 








 
   جدول کمکی اعداد
  این جدول که شامل یک ستون و مقادیری از 1 تا N می باشد در حل مساله ها به شیوه ی Set-based مورد استفاده قرار می گیرد.
   SQL Server
   ۱۴۹۱۴
   این مقاله حاوی فایل ضمیمه نمی باشد
   محمد سلیم آبادی
   ۱۳۸۹/۲/۱۶
نسخه قابل چاپ نسخه قابل چاپ

جدول کمکی اعداد

یک جدول کمکی اعداد یک ابزار قدرتمند هست که در حل مساله ها یا خلق query های متعدد از آن استفاده می شود.
یک جدول کمکی اعداد (که Nums نام گذاری می شود) یک جدول ساده هست که شامل اعدادی از 1 تا N می شود.
برای انتشار اعداد در جدول روش های بسیار متنوعی وجود دارد که در ادامه به برخی از آنها اشاره می کنم.

لیست روشها:

           1. Simple Loop

           2. Ben Gan Loop

           3. Simple Recursive CTE

           4. Recursive CTE and ROW_NUMBER (Owner)

           5. nested CTE (Itzik Ben Gan method)

           6. mathematical method

           7. ROW_NUMBER with existing table

           8. Set Based Approach instead of ROW_NUMBER()

           9. using master..spt_values

          10. etc

 

حلقه ساده

در این روش در یک حلقه از عدد 1 تا عدد N را در جدول درج می کنیم. به این صورت:

IF OBJECT_ID('dbo.Nums'IS NOT NULL
  DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT@i AS INT;
SET @max = 1000000;
SET @i = 1;

WHILE @i <= @max
BEGIN
      INSERT INTO dbo.Nums (n) VALUES(@i);
      SET @i = @i + 1;
END

یا خیلی ساده تر با کمک خصیصه ی IDENTITY و تابع SCOPE_IDENTITY روش حلقه ی ساده به شکل زیر در خواهد آمد:

CREATE TABLE dbo.Numbers 

    Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED 

 
WHILE COALESCE(SCOPE_IDENTITY(), 0) <= 1000000 
    INSERT dbo.Numbers DEFAULT VALUES

این روش را اصلا پیشنهاد نمی کنم، چون بسیار غیر کارامد می باشد. این روش را در کامپیوترم اجرا کردم که بعد از دو دقیقه انتظار در حالی که query هنوز در حال اجرا شدن بود آن را Cancel کردم!


حلقه آقای BG

آقای Itzik Ben Gan در کتابهای سری Inside مربوط به T-SQL از روشی استفاده کرده اند که جالب است. این حلقه بر خلاف حلقه ای که قبلا معرفی شد به تعداد بسیار کمی اجرا می شود.

DECLARE @max AS INT@rc AS INT;
SET @max = 1000000;
SET @rc = 1;

INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
  INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
  SET @rc = @rc * 2;
END 
INSERT INTO dbo.Nums
  SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;

دلیل اصلی سریع بودن این روش به خاطر به حداقل رساندن تعداد عبارت های INSERT می باشد که نتیجه ی آن درج کمتر در transaction log است.

Simple Recursive CTE

;WITH RecurCTE(n) AS
 (SELECT 1 
  UNION ALL
  SELECT n + 1 FROM RecurCTE WHERE n < 1000000)
  SELECT n FROM RecurCTE
 OPTION (MAXRECURSION 0)

Recursive CTE and ROW_NUMBER

;WITH R_CTE(i) AS
 (SELECT 1 
  UNION ALL
  SELECT i + 1 FROM R_CTE WHERE i < 100)
  
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS n 
  FROM R_CTE AS C1
       CROSS APPLY R_CTE AS C2
       CROSS APPLY R_CTE AS C3;

Ben Gan Approach

;WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
  SELECT n FROM Nums WHERE n <= 1000000;

روش ها بسیار گسترده و متنوع هستند به همین علت پیشنهاد می کنم به مقاله های معرفی شده برای آشنایی بیشتر رجوع کنید. در لینک های زیر هم به طریقه تولید این جدول اشاره شده است و همچنین کاربردی که در حل مساله های می تواند داشته باشد.

مراجع و اطلاعات بیشتر

simple-talk

sqlmag.com (Itzik Ben Gan - SQL Server MVP)

Plamen Ratchev (SQL Server MVP)

SQLServeCentral.com (Michael Coles - SQL Server MVP)

http://www.projectdmx.com/tsql/tblnumbers.aspx

Jef Moden Answer (SQL Server MVP)

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html