Hello,

personnally, I would use some statements along the lines of the ones below (taken from an article by Y.Ben-Gan, on www.sqlmag.com; search in google for the words CREATE TABLE NUMS). The idea is to populate the table exponentially, by doubling the number of records at each iterations.

In order to achieve the desired results, one would populate the table by setting @max = (@end - @start +1) where @start and @end denotes the desired range, and then issuing an update statement like

UPDATE nums set n = n -1 + @start

I wonder how this solution would compare to the others in terms of performance ...

Sincerly,

Emmanuel Nanchen

CREATE TABLE Nums(n INT NOT NULL);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000; -- revise max number according to your needs
SET @rc = 1;

BEGIN TRAN
  INSERT INTO Nums VALUES(1);

  WHILE @rc * 2 <= @max
  BEGIN
    INSERT INTO Nums SELECT n + @rc FROM Nums;
    SET @rc = @rc * 2;
  END

  INSERT INTO Nums
    SELECT n + @rc FROM Nums WHERE n + @rc <= @max;
COMMIT TRAN

ALTER TABLE Nums ADD CONSTRAINT PK_Nums PRIMARY KEY(n);

I am dealing long enough with number ranges. I did tried approaches similar to ones described in this article. Sadly, the only really fast solution here is the dumbest one.

Create a table NUMS and fill it with as many numbers as needed. Add index or primary key. Then use this pre-filled table in any sql statements that require sequential range of numbes. This is really fast. It takes less that a second in my application.

I can't find the article by Itzik Ben Gan - can someone please post a link.

Any idea when he 'came up with' the algorithm? I ask because it is suspiciously like the one I posted on here in September 2002!

http://www.sqlservercentral.com/scripts/viewscript.asp?scriptid=321