百萬行資料查詢效能比較

----------------------該方案37秒加5秒
set nocount on
if object_id('dbo.Nums')is not null
drop table dbo.Nums;
go
create table dbo.Nums ( n int not null primary key)
declare @max as int,@rc as int
set @max=1000000
set @rc=1

insert into Nums values(1)
while @rc*2<=@max
begin
insert into dbo.Nums select n+@rc from dbo.Nums
set @rc=@rc*2;
end

insert into dbo.Nums
select n+@rc from dbo.Nums where =@n
option (maxrecursion 0)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7899089/viewspace-611115/，如需轉載，請註明出處，否則將追究法律責任。