Sunday, April 02, 2006

Random record CTE type thing

One of the developers asked me if it was possible to repeat a single query multiple times. He was trying to generate test data for stress testing and wanted to repeat a simple SELECT ID, NewID(). He told me Oracle could do it so I obviously had to prove that SQL Server was able to do this too :-)

Here is the solution I came up with, it's a little fun with CTEs and the RowNumber function.

WITH RepeatCTE (RowNumber, LogID)
AS
( SELECT ROW_NUMBER() OVER(ORDER BY newID()) as RowNumber, newID() LogID UNION ALL
SELECT RowNumber + 1 as RowNumber, newID() LogID FROM RepeatCTE
)
SELECT TOP 400 * FROM RepeatCTE OPTION (MAXRECURSION 0);

3 comments:

tv said...

This is not the original solution you came up with, because that one included a MAXRECURSION of 400.
I didn't know the 0 though, so thanks for that. Still a bit tricky though because when you forget the TOP-clause... ouch!
Also interesting to see the difference in the execution plan when changing the MAXRECURSION from the infinite 0 to a fixed amount (watch out for the assert!).

SQL said...

This is pretty cool I usually have an auxillary (number) table that I use for date ranges and other stuff
Then my query would look like this

select NumberID,NEWID() from dbo.NumberPivot
where NumberID < 400

Or to build a date dimension you could do something like this
--Create numbers table (once only)
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)

DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0

WHILE @intLoopCounter <=1000
BEGIN
INSERT INTO NumberPivot
VALUES (@intLoopCounter)

SELECT @intLoopCounter = @intLoopCounter +1
END
GO


declare @d datetime
select @d =getdate()

select * ,dateadd(d,NumberID,@d) as date,
datepart(wk,dateadd(d,NumberID,@d)) as Week,
datepart(m,dateadd(d,NumberID,@d)) as 'Month',
datepart(qq,dateadd(d,NumberID,@d)) as Quarter,
datepart(yyyy,dateadd(d,NumberID,@d)) as Year,
datepart(dw,dateadd(d,NumberID,@d)) as DayOfWeek,
case when datepart(dw,dateadd(d,NumberID,@d)) between 2 and 6 then 1 else 0 end as WeekDay,
convert(int,convert(varchar,dateadd(d,1,dateadd(d,NumberID,@d)),112)) as Isodate
from Numberpivot

or
-- next 100 weeks from 2000-01-01
DECLARE @dtmDate DATETIME
SELECT @dtmDate = '2000-01-01 00:00:00.000'
SELECT DATEADD(wk,numberID,@dtmDate)
FROM dbo.NumberPivot
WHERE NumberID < 100

Number tables are very handy as you can see

WesleyB said...

Thanks for the great tips. Especially since my solution requires SQL Server 2005 and we all know not everyone is lucky enough to work with it :-(