جدول کمکی اعداد
یک جدول کمکی اعداد یک ابزار قدرتمند هست که در حل مساله ها یا
خلق
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