Search Wiki:
Resource Page Description
This TVF generates a contiguous sequence of integers. Useful for generating test data, populating calendars, etc.

This is a Table-Valued User Defined Function to generate all the integers between two endpoints. It's surprisingly fast, easilly beating my best effors at writing a CLR UDF to do this.

Use it like this

select Seq.Value
from Utils.Sequence(1,1000) seq

or

select dateadd(d,Seq.Value,'2000-01-01') d
from Utils.Sequence(1,2000) seq

to generate a calendar.


NB: this is not a replacement for IDENTITY columns or a oracle sequence work-alike.

NB2: This query uses a stack of Common Table Expressions to generate the numbers, and the original implementation is credit to MVP's Steve Kass and Itzik Ben-Gan.

David
Last edited Apr 30 2008 at 10:25 PM  by dbrowne, version 6
Comments
Atul wrote  Aug 12 2008 at 6:42 AM  
It really works fast. But I have a question

I see in MSDN that NOEXPAND applies to indexed views.
NOEXPAND
Specifies that any indexed views are not expanded to access underlying tables when the query optimizer processes the query. The query optimizer treats the view like a table with clustered index. NOEXPAND applies only to indexed views.

I checked the execution plan with and without NOEXPAND hint, it does not show any difference. Could you please share your thought, whether I am missing very basic thing.

dbrowne wrote  Sep 26 2008 at 10:25 PM  
Yeah, I don't think it's necessary. It used to be an inline TVF, which is really s different beast. I started getting some bad plans when using it in a complicated query, so I was trying to force the sequence to be materialized in the query plan. That's why I eventually changed it to a multi-statement TVF.

David

krishna282 wrote  Feb 12 2010 at 10:02 PM  
There are 6 derived tables used in the function L1 - L6, but L6 is never used, only L5. I suppose that's a typo?

Updating...
Page view tracker