Create A Number Table

• Tweet
• 

        Created 08/26/05 by Oskar Austegard (<a href="http://mo.notono.us">http://mo.notono.us</a>) from article at 
<a href="http://msdn.microsoft.com/library/en-us/dnsqlpro03/html/sp03k1.asp">http://msdn.microsoft.com/library/en-us/dnsqlpro03/html/sp03k1.asp</a>
Can be used inline in functions, or to create a standalone Numbers table (as required by <a href="http://www.bigbold.com/snippets/posts/show/1183">dbo.Split</a>).

--Creates a table of sequential numbers, useful for all sorts of things
--Created 08/26/05 by Oskar Austegard from article at 
--http://msdn.microsoft.com/library/en-us/dnsqlpro03/html/sp03k1.asp
--Limits: @Min and @Max must be between -2147483647 and 2147483647, including.
--If @Max <= @Min, only a single record with @Min is created
ALTER FUNCTION dbo.NumberTable (@Min int, @Max int)
RETURNS @T TABLE (Number int NOT NULL PRIMARY KEY)
AS
BEGIN
  -- Seed the table with the min value
  INSERT @T VALUES (@Min)
  --Loop until all the rows are created, inserting ever more records for each iteration (1, 2, 4, etc)
  WHILE @@ROWCOUNT > 0
	BEGIN
	  INSERT @T 
	  --Get the next values by adding the current max - start value + 1 to each existing number
	  --need to calculate increment value first to avoid arithmetic overflow near limits of int
	  SELECT t.Number + (x.MaxNumber - @Min + 1)
	  FROM @T t
	    CROSS JOIN (SELECT MaxNumber = MAX(Number) FROM @T) x --Current max
	  WHERE
	    --Do not exceed the Max - shift the increment to the right side to take advantage of index
	    t.Number <= @Max - (x.MaxNumber - @Min + 1)
	END
  RETURN
END

Tags:
• monotonous
• numbers
• SQL
• udf