sql server 2008 - Efficient way of filling up the table with million empty rows -

do know efficient way of filling table huge number of empty rows? inserting them using while clause not efficient. there better way of filling table empty rows this:

declare @cnt int set @cnt = 1 while @cnt < 3 begin insert tab values( '1' ) set @cnt = @cnt + 1 end 

i know, if possible display let 1 mil rows (numbered 1 1000000) using select clause , cte (common table expressions) without using existing table.

i appreciate help.

here way generate sequence of numbers:

select top (1000000) n = convert(int, row_number() on (order s1.[object_id])) yourtable sys.all_objects s1 cross join sys.all_objects s2 option (maxdop 1);  create unique clustered index n on yourtable(n) -- (data_compression = page) ; 

see sql fiddle demo of 100 numbers being created.

this code @aaron bertrand's article generate set or sequence without loops – part 1. article includes other methods generate number sequence.