چند روز پیش برای منظور خاصی نیاز داشتم تا سمت سرور تعداد زیادی عدد متوالی در Rangeهای مورد نیاز کاربر تولید کنم و حاصل رو با جدول دیگه ای Join کنم. اولین روشی که به ذهن هر برنامه نویسی میرسه (از جمله خود من!) این هست که با Insert کردن به یک جدول در جریان Loop اعداد رو تولید کنیم. هم از یک متغیر برای شمارنده و هم از خاصیت Identity میتونیم استفاده کنیم:
DECLARE @Counter SMALLINT
SET @Counter=1
DECLARE @Output TABLE (c1 SMALLINT)
WHILE @Counter<=10000
BEGIN
INSERT @Output VALUES(@Counter)
SET @Counter=@Counter+1
END
SELECT * FROM @Output
البته در صورت استفاده از Identity، باید مقدار آخرین Identity تولید شده رو کنترل کنیم تا حلقه به تعداد دلخواه اجرا بشه.
به ذهنم رسید اگر یک جدول تک فیلدی با مقدار 1 داشته باشیم و بتونیم Queryهای تو در تو یا Recursive روی اون اجرا کنیم، منطقا میشه مقدار اولیه 1 رو در خلال Query زیاد کرد و به عدد دلخواه رسوند. در SQL Server 2005 وقتی اسم Recursive میاد، اولین چیزی که انسان به خاطر میاره CTE هستش! ابتدا پیاده سازی این کار با CTE گنگ به نظر میرسید اما با توانایی CTE این کار به شکل جالبی انجام شد!
;WITH MyCTE AS
(SELECT t1.* FROM (SELECT 1 AS c1,1 AS Level) t1
UNION ALL
SELECT t2.*,MyCTE.Level+1 FROM (SELECT 1 AS c1) t2 JOIN MyCTE ON t2.c1=MyCTE.c1
WHERE MyCTE.Level<=9999)
SELECT Level FROM MyCTE
OPTION (MAXRECURSION 0)
نکته ظریف کار اینجاست که جداول مجازی t1 و t2 هر دو، عدد ثابت 1 رو بعنوان فیلد c1 تا آخر کار باید استفاده کنند تا همیشه Join بینشون جواب بده.
اما صرف نظر از محقق شدن هدف به این روش، چیزی که من رو غافلگیر کرد Performance قابل توجه CTE بود! اگر چه دستور SET STATISTICS TIME ON میتونه جزئیات زمانی که برای انجام کار صرف شده رو نمایش بده، اما مطالعه نتایج این دستور برای روش اول کمی مشکله چون تعداد زیادی INSERT داره. لذا برای اینکه راحت تر به اختلاف سرعت بین این دو روش پی ببریم از این ترفند قدیمی استفاده میکنیم، مقایسه ساعت قبل و بعد از اجرای دستور:
روش اول:
DECLARE @time1 DATETIME, @time2 DATETIME
SET @time1=GETDATE()
DECLARE @Counter SMALLINT
SET @Counter=1
DECLARE @Output TABLE (c1 SMALLINT)
WHILE @Counter<=10000
BEGIN
INSERT @Output VALUES(@Counter)
SET @Counter=@Counter+1
END
SELECT * FROM @Output
SET @time2=GETDATE()
SELECT DATEDIFF (ms,@time1,@time2)
روش دوم:
DECLARE @time1 DATETIME, @time2 DATETIME
SET @time1=GETDATE()
;WITH MyCTE AS
(SELECT t1.* FROM (SELECT 1 AS c1,1 AS Level) t1
UNION ALL
SELECT t2.*,MyCTE.Level+1 FROM (SELECT 1 AS c1) t2 JOIN MyCTE ON t2.c1=MyCTE.c1
WHERE MyCTE.Level<=9999)
SELECT Level FROM MyCTE
OPTION (MAXRECURSION 0)
SET @time2=GETDATE()
SELECT DATEDIFF (ms,@time1,@time2)
در آخرین دستور، تابع DATEDIFF اختلاف دو زمان رو بر حسب میلی ثانیه نشون میده. تفاوت Performance در Rangeهای بزرگتر به مراتب بیشتر هم میشه.
اما جذابیت CTE به همینجا ختم نمیشه! CTE در توابع و Viewها قابل استفاده هستش و ما میتونیم در کنار Inline Table Valued Functions کار رو به شکل قشنگ تری ارائه کنیم:
CREATE FUNCTION fn_GetRows(@From INT, @TO INT)
RETURNS TABLE AS RETURN
WITH MyCTE AS
(SELECT t1.* FROM (SELECT 1 AS c1,@From AS Level) t1
UNION ALL
SELECT t2.*,MyCTE.Level+1 FROM (SELECT 1 AS c1) t2 JOIN MyCTE ON t2.c1=MyCTE.c1
WHERE MyCTE.Level<=@TO-1)
SELECT Level FROM MyCTE
GO
SELECT * FROM fn_GetRows(1,10000)
OPTION (MAXRECURSION 0)
SELECT * FROM fn_GetRows(-500,500)
OPTION (MAXRECURSION 0)
حالا fn_GetRows رو میشه با جداول دیگه Join کرد.
علت استفاده از OPTION MAXRECURSION این هست که بطور پیش فرض، CTE تا 100 مرتبه تو در تو میشه، اما عدد صفر در OPTION به معنی درخواست برای نامحدود بودن این عمل هست.
داشتن تابعی مثل fn_GetRows در جای خودش نیکوست! مثلا اگر جدول Orders فیلد Identity بعنوان OrderID استفاده کرده و شما قصد دارین OrderIDهای حذف شده (یا به عبارتی Gapهای موجود) رو تشخیص بدین به کمک fn_GetRows و یک Outer Join این کار امکان پذیره. برای نمونه من جدول Gaps رو میسازم و چند عدد که بینشون فاصله هست وارد میکنم و بعد Gapها رو با fn_GetRows بدست میاریم:
CREATE TABLE Gaps(
c1 INT)
GO
INSERT Gaps VALUES(1)
INSERT Gaps VALUES(3)
INSERT Gaps VALUES(6)
INSERT Gaps VALUES(9)
INSERT Gaps VALUES(11)
GO
SELECT f.* FROM
(SELECT (SELECT MIN(c1) FROM Gaps) AS MinC1,(SELECT MAX(c1) FROM Gaps) AS MaxC1) MinMax
CROSS APPLY fn_GetRows(MinC1,MaxC1) f
LEFT JOIN Gaps g ON f.Level=g.c1
WHERE g.c1 IS NULL
OPTION (MAXRECURSION 0)
و در خاتمه:
زبان قدرتمند TSQL در SQL Server 2005 سعی در ارث بری هر چه بیشتر از استاندارد ANSI SQL داشته و به بلوغ خاصی رسیده. دستورات TSQL قابلیتهایی دارن که نه تنها در نگاه اول، بلکه حتی در نگاه دوم هم قابل رویت نیستند!! اما با دقت بیشتر و درک بهتر از نحوه عملکرد اونها کارهای جدیدی امکان پذیر میشن. حتی بعضا این قابلیتها فراتر از ANSI، و البته خاص TSQL هستند. مثلا Table Operatorهایی از جمله PIVOT, UNPIVOT و APPLY (که در مثال بالا استفاده شد) در ANSI SQL وجود ندارند.
پس TSQL را پاس بداریم!