create proc GenTable1 @max int
as 
begin
	declare @Min int
	set @Min = 1
	set @Max = 8000
	DECLARE @T TABLE (Number int NOT NULL PRIMARY KEY)
	INSERT @T VALUES (@Min) -- Seed the table with the min value
	WHILE @@ROWCOUNT > 0	--Loop until all the rows are created, inserting ever more records for each iteration (1, 2, 4, etc)
		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
	select count(*) from @T
end
go

create proc GenTable2 @max int
as
begin
	DECLARE @T2 TABLE (Number int NOT NULL PRIMARY KEY)

	declare @I int
	set @I = 1
	WHILE @I <= 8000 begin
		insert into @T2 (Number) values (@I)
		set @i = @I+1
	END
	select count(*) from @T2
end
go

exec GenTable1 8000
exec GenTable2 8000

quote:

---

Originally posted by Wanderer

Difficult to prove for definitely atm, but I am sure that Michaels is better. generating query plans is problematic for comparison, due to the number of plans generated by iteratives - Michaels shows 99% of all execution, but runs in <1 second, whereas other version takes <2 seconds.

I will need to consider the support side - Michaels is significantly more complex, but I think the end answer will (hopefully) be the best performing solution.

anyone got anything better?

*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

---