I modified your function as follows:
CREATE
FUNCTION dbo.MySeqDates (@LowDate DATETIME, @HighDate DATETIME)
RETURNS @Dates TABLE (SeqDate DATETIME)
AS
BEGIN
DECLARE @Temp DATETIME
IF @LowDate > @HighDate
SELECT @Temp = @LowDate,
@LowDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0),
@HighDate = DATEADD(day, DATEDIFF(day, 0, @Temp), 0)
ELSE
SELECT @LowDate = DATEADD(day, DATEDIFF(day, 0, @LowDate), 0),
@HighDate = DATEADD(day, DATEDIFF(day, 0, @HighDate), 0)
INSERT @Dates (SeqDate) VALUES (@LowDate)
DECLARE @TotalRows int, @RowCnt int
SET @TotalRows=1
SET @RowCnt=1
WHILE @RowCnt > 0 BEGIN
INSERT @Dates (SeqDate)
SELECT DATEADD(dd, @TotalRows, d.SeqDate)
FROM @Dates d
WHERE DATEADD(dd, @TotalRows, d.SeqDate) <= @HighDate
SET @RowCnt=@@ROWCOUNT
SET @TotalRows=@TotalRows+@RowCnt
END
RETURN
END
The difference is that I use a variable to count the number of rows (instead of using COUNT() in a derived table) which should be faster if the number of rows is really big. To benchmark, try running something like this:
DECLARE
@t datetime
SET @t=GETDATE()
SELECT COUNT(*) FROM dbo.fnSeqDates('20060101','23070131')
PRINT CONVERT(varchar(10),DATEDIFF(ms,@t,GETDATE()))+' ms'
SET @t=GETDATE()
SELECT COUNT(*) FROM dbo.MySeqDates('20060101','23070131')
PRINT CONVERT(varchar(10),DATEDIFF(ms,@t,GETDATE()))+' ms'
Of course, it's very unlikely that anyone would need such a long period (and for shorter periods the difference in performance is very small).
Razvan