RSS

Tag Archives: numbers

tsql: give me 5! (and 6, 7, 8, …)

Sometimes on databases I’ve worked on there were “table of numbers”, i.e. to store Years.
Are you sure you really need that table? There are a lot of -funny- ways to get numbers from sql! C’mon!

0-999 numbers, using a Table Value Constructor (http://msdn.microsoft.com/en-us/library/dd776382.aspx)

;WITH tmp AS (
  SELECT * FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS X(y)
)
SELECT
  a.y + (b.y * 10) + (c.y*100) as Value
FROM tmp as a, tmp as b, tmp as c
ORDER BY Value

These are years from 1900 to 3947, is it enough?

select 1900 + number as [Year]
  from master..spt_values
  where type = 'P'

This is a more comprehensive list of numbers between 0 and 65535, aka (256 * 256) – 1

;with seed as (
  select number
  from master..spt_values
  where type = 'P'
  and number < 256
)
select n1.number + n2.number * (256)
  from seed n1
  full outer join seed n2 on 1=1
 
Leave a comment

Posted by on 2016/06/01 in dev

 

Tags: , , , ,