MSDN Archive Home
Help and FAQs
SQL Sequence Generator
All Resource Updates
Change History (all pages)
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
from Utils.Sequence(1,1000) seq
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.
Apr 30 2008 at 10:25 PM
, version 6
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.
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.
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.
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?
Sign in to add a comment
Sun Feb 17 2008 at 8:00 AM
Related Resource Pages
David Browne's Code Samples
More Tags ...
Visual Studio 2005
Visual Studio 2008
Visual Studio 2010
Manage Your Profile
MSDN Flash Newsletter
© 2008 Microsoft Corporation. All rights reserved.