Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Create a generate_series table value function

Postgres has this function and it can be very useful.
The generate_series(start, stop, step) will return a single column of table from start to stop incremented by step inclusively. (e.g. 1 to 10, step 2 will return 1,3,5,7,9). Postgres also offers an overload that supports date incremented by an interval, although that can be emulated effectively by::
select dateadd(d,n,@myStartDate) from generate_series(0,100,1) f(n)

While the function can be implemented in SQL server using a Itzik style cross join based solution, a physical table, or using a sql clr function both have their issues.

The cross joined solution can be very expensive, and radically increase the complexity of a plain. It also degrades quickly and starts being the bottle neck in the query when asked to produce 1 million rows.

The physical or even the in memory optimized version has the disadvantage that to schema bind it you need to create this table in every database. For large number tables (>1m record) that be costly . (10million records would use over 260 megabytes).

The other option, SQL CLR sTVF has an unfortunate row goal of 1000 baked in. This reduces the effectiveness of the function when asked to select anything more than 1000 entries, and can produce a bad plan.
The benefit of having the function approach allows for a much more optimal row goal to be computed be done at a language level, as that it allows the row goal to be computed as part of the plan instead of treating it as a black box

1 vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base