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);
Subscribe to:
Post Comments (Atom)
3 comments:
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!).
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
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 :-(
Post a Comment