SQL 機生成100w條有規律的數據--

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


Create   proc timecount
@sql varchar(4000)
as 
declare @t1 datetime,@t2 datetime
set @t1=getdate()
exec (@sql)
set @t2=getdate()
select timeuse=cast (datediff(ms,@t1,@t2) as varchar(30)) +'ms'


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

----create by talantlee--
----需要時間
truncate  table test100w
---------------------
create table test100w
(tid int identity(1,1),
tname varchar(30),
ttime datetime,
enable int)
------------------------------------------

select convert (int,rand(checksum(newid())) *2)--enable 產生0或1

--------------------------------------------------------------
declare @a int
set @a=convert (int,rand(checksum(newid())) *30)----30天之內，你可以自己選擇
SELECT Convert(char(10),DATEADD(day, @a, getdate()),120) ----當前日期+隨機數
-----上面生成日期，隨機在30天內相加
----------------------------------------------------

update a set b=cast(a as varchar(30))+'name'---產生name
---------------------------------------

-------測試一--------------------
 declare @c datetime
declare @b int
declare @a int
set @b=0
set @c=getdate()

while(@b<1000000)
begin
set @a=convert (int,rand(checksum(newid())) *30)
insert into test100w(tname, ttime,enable)values(1, Convert(char(10),DATEADD(day, @a, getdate()),120) , convert (int,rand(checksum(newid())) *2))
set @b=@b+1
end
 print datediff(ms,@c,getdate())
-----用時間為268876ms ---第2次257170

timecount 'update test100w set tname=''name''+cast(tid  as varchar(30))'
-----用時間為76330ms ---第2次76670--------總時間:345206 第2次333840

---------------測試二-----------------------------------------------------------------------
 declare @c datetime
declare @b int
declare @a int
set @b=0
set @c=getdate()

while(@b<1000000)
begin
set @a=convert (int,rand(checksum(newid())) *30)
insert into test100w( ttime,enable)values( Convert(char(10),DATEADD(day, @a, getdate()),120) , convert (int,rand(checksum(newid())) *2))
set @b=@b+1
end
 print datediff(ms,@c,getdate())
---測試時間341986-----------------------
timecount 'update test100w set tname=''name''+cast(tid  as varchar(30))'
-----------------------------測試時間155343------總時間497329----二種方法相差152123ms
----效率低44.1%
-----得出結論
--1.update 比插入快
--2 應該避免null值插入