sql server identity函数插入连接数值的用法。

SQL在1秒内插入100万行数字列的语句

 

 

DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHEdeclare @beginTime datetimeset @beginTime=getdate()SELECT TOP 1000000 IDENTITY(INT,0,1) AS idx INTO dbo.t_Numbers FROM master.sys.all_columns c1 CROSS JOIN master.sys.all_columns c2select datediff(ms,@beginTime,getdate()) as [Time(ms)]/*(1000000 行受影响)Time(ms)----------8563*/-- 利用row_number更加快速地创建和填充数字表；100万数字表1s内就生成好了drop table t_NumbersDBCC DROPCLEANBUFFERSDBCC FREEPROCCACHE declare @beginTime datetimeset @beginTime=getdate() SELECT TOP 1000000 ROW_NUMBER() over (order by c1.object_id) AS N INTO dbo.t_Numbers FROM master.sys.all_columns c1 CROSS JOIN master.sys.all_columns c2select datediff(ms,@beginTime,getdate()) as [Time(ms)]/*(1000000 行受影响)Time(ms)-----------843*/