Create A Number Table
Join the DZone community and get the full member experience.Join For Free
Created 08/26/05 by Oskar Austegard (http://mo.notono.us) from article at http://msdn.microsoft.com/library/en-us/dnsqlpro03/html/sp03k1.asp Can be used inline in functions, or to create a standalone Numbers table (as required by dbo.Split).
--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
Opinions expressed by DZone contributors are their own.