百萬條資料產生比較 快慢

 

alter table tmpdb
(
 id int identity(1,1) primary key,
 num varchar(8),
 st varchar(8)
)
GO
--

select * from tmpdb
--

declare @dt Datetime
set @dt = getDate()
set nocount off
declare @row int
set @row = 1000000
while @row > 0
begin
 Raiserror('need %d rows', 10, 1, @row) with nowait
 set rowcount @row
 insert tmpdb select num = convert(char(6),rand()*1000000),
  st = lower(left(newid(),6))
 set @row = @row - @@rowcount
end
SELECT BeginDate=@dt, EndDate=getDate(), Second=Datediff(Second, @dt, getDate())
go
select count(id) from tmpdb
go

-- 共計耗時：1小時29分19秒

-----------------------------------------------------
create table tmpdbonlynum
(
 id int identity(1,1) primary key,
 num varchar(8),
 st varchar(8)
)
go

declare @dt Datetime
set @dt = getDate()
set nocount off
declare @row int
set @row = 1000000
while @row > 0
begin
 Raiserror('need %d rows', 10, 1, @row) with nowait
 set rowcount @row
 INSERT tmpdbonlynum SELECT
 num = RIGHT(100000000 + CONVERT(bigint, ABS(CHECKSUM(NEWID()))), 8),
 st = RIGHT(CONVERT(varchar, left(NEWID(), 8)), 8)  -- 產生隨機數字加字元
 --FROM syscolumns c1, syscolumns c2  -- SET NOCOUNT ON 49 Second
 FROM syscolumns c1, sysobjects o
-- insert tmpdbonlynum select num = convert(char(6),rand()*1000000),
--  st = lower(left(newid(),6))
 set @row = @row - @@rowcount
end
SELECT BeginDate=@dt, EndDate=getDate(), Second=Datediff(Second, @dt, getDate())
go
select count(id) from tmpdbonlynum
go

-- 共計耗時：34秒