RSS 2.0
Sign In
# Saturday, September 1, 2012

Back in 2006 and 2007 we have defined dbo.Numbers function: Numbers table in SQL Server 2005, Parade of numbers. Such construct is very important in a set based programming. E.g. XPath 2 contains a range expression like this: "1 to 10" to return a sequence of numbers. Unfortunately neither SQL Server 2008 R2, nor SQL Server 2012 support such construct, so dbo.Numbers function is still actual.

After all these years the function evolved a little bit to achieve a better performance. Here is its source:

-- Returns numbers table.
-- Table has a following structure: table(value int not null);
-- value is an integer number that contains numbers from 1 to a specified value.
create function dbo.Numbers
(
  -- Number of rows to return.
  @count int
)
returns table
as
return
  with Number8 as
  (
    select
      *
    from
      (
        values
          (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0),
          (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0),
          (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0),
          (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0),
          (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0),
          (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0),
          (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0),
          (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0),
          (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0),
          (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0),
          (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0),
          (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0),
          (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0),
          (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0),
          (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0),
          (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
      ) N(Value)
  ),
  Number32(Value) as
  (
    select
      0
    from
      Number8 N1
      left join
      Number8 N2
      on
        @count > 0x100
      left join
      Number8 N3
      left join
      Number8 N4
      on
        @count > 0x1000000
      on
        @count > 0x10000
  )
  select top(@count) row_number() over(order by @count) Value from Number32;

Saturday, September 1, 2012 8:16:49 PM UTC  #    Comments [0] -
SQL Server puzzle | Tips and tricks
All comments require the approval of the site owner before being displayed.
Name
E-mail
Home page

Comment (Some html is allowed: a@href@title, b, blockquote@cite, em, i, strike, strong, sub, super, u) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

[Captcha]Enter the code shown (prevents robots):

Live Comment Preview
Archive
<September 2019>
SunMonTueWedThuFriSat
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345
Statistics
Total Posts: 366
This Year: 2
This Month: 0
This Week: 0
Comments: 226
Locations of visitors to this page
Disclaimer
The opinions expressed herein are our own personal opinions and do not represent our employer's view in anyway.

© 2019, Nesterovsky bros
All Content © 2019, Nesterovsky bros
DasBlog theme 'Business' created by Christoph De Baene (delarou)