临时表各方式对比
USE tempdb
GO
SET STATISTICS TIME OFF
SET STATISTICS io OFF
drop table #result
create table #result
(
[插入行数] int,
[正常临时表] int,
[新加GUID列的临时表] int,
[新加自增列临时表] int,
列集 int,
[into方式创建临时表] int
)
set nocount on
declare @i int = 0
while @i<=30
begin
begin try
drop table #Studentid
drop table #Studentid1
drop table #Studentid2
drop table #Studentid3
drop table #Studentid4
end try
begin catch
print ''
end catch
declare @top int
select @top=CAST((
select count (1) from SGPZ..student
)*RAND() AS INT)
-- select @top=CAST(10000*RAND() AS INT)
--正常表
CREATE TABLE #Studentid
(
[StuID] [int] NOT NULL,
[StuNum] [varchar](50) NOT NULL,
[StuNamePY] [varchar](50) NOT NULL,
[StuNameWB] [varchar](50) NOT NULL,
[StuName] [varchar](50) NOT NULL,
[IDNumber] [varchar](18) NOT NULL,
[FileNo] [varchar](50) NULL,
[DocType] [varchar](50) NULL,
[Birthday] [varchar](10) NOT NULL,
[Sex] [int] NOT NULL,
[Height] [int] NULL,
[DocAddress] [varchar](200) NULL,
[Birthplace] [varchar](50) NULL,
[ISGOT] [int] NOT NULL,
[Address] [varchar](200) NULL,
[ResTerm] [varchar](50) NULL,
[Mobile] [varchar](50) NULL,
[Tel] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[ShuttleAddress2] [varchar](100) NULL,
[ShuttleAddress1] [varchar](100) NULL,
[VisitTime] [varchar](50) NULL,
[ArrangeClass] [varchar](50) NULL,
[BusinessWay] [varchar](50) NULL,
[BusinessType] [varchar](50) NULL,
[BusinessSources2] [varchar](50) NULL,
[BusinessSources3] [varchar](50) NULL,
[CoachDesEmpID] [int] NULL,
[CoachDesEmpName] [varchar](50) NULL,
[StuCoachEmpID] [int] NULL,
[StuCoachEmpName] [varchar](50) NULL,
[Class] [varchar](50) NULL,
[ClassName] [varchar](4) NOT NULL,
[ApplyType] [varchar](10) NULL,
[CarType] [varchar](50) NOT NULL,
[TransferType] [int] NULL,
[MarketType] [varchar](50) NULL,
[MarketContent] [varchar](100) NULL,
[Gifts] [varchar](50) NULL,
[OfferWay] [varchar](50) NULL,
[CouponNo] [varchar](50) NULL,
[CoType] [varchar](50) NULL,
[IsGroup] [int] NOT NULL,
[Remark] [varchar](2000) NULL,
[EntryDate] [datetime] NOT NULL,
[DjfNum] [varchar](50) NULL,
[AreaOrganID] [int] NOT NULL,
[AreaPointCode] [varchar](50) NOT NULL,
[DeptOrganID] [int] NOT NULL,
[DeptPointCode] [varchar](50) NOT NULL,
[ConEmpID] [int] NOT NULL,
[ConEmpName] [varchar](50) NOT NULL,
[ConEmpTel] [varchar](50) NULL,
[InDate] [datetime] NOT NULL,
[AddEmpID] [int] NOT NULL,
[AddEmpName] [varchar](50) NOT NULL,
[OrganID] [int] NOT NULL,
[JlClassName] [varchar](50) NULL,
[IsDel] [int] NOT NULL,
[AuState] [int] NOT NULL,
[AuEmpID] [int] NULL,
[AuTime] [datetime] NULL,
[IsInternal] [int] NOT NULL,
[StuInTime] [datetime] NULL,
[SLRQ] [datetime] NULL,
[LSH] [int] NULL,
[StuType] [int] NOT NULL,
[PrioCoachID] [int] NULL,
[PrioCoachName] [varchar](50) NULL
)
--GUID
CREATE TABLE #Studentid1
(
GUIDID uniqueidentifier DEFAULT (NEWSEQUENTIALID()),
[StuID] [int] NOT NULL,
[StuNum] [varchar](50) NOT NULL,
[StuNamePY] [varchar](50) NOT NULL,
[StuNameWB] [varchar](50) NOT NULL,
[StuName] [varchar](50) NOT NULL,
[IDNumber] [varchar](18) NOT NULL,
[FileNo] [varchar](50) NULL,
[DocType] [varchar](50) NULL,
[Birthday] [varchar](10) NOT NULL,
[Sex] [int] NOT NULL,
[Height] [int] NULL,
[DocAddress] [varchar](200) NULL,
[Birthplace] [varchar](50) NULL,
[ISGOT] [int] NOT NULL,
[Address] [varchar](200) NULL,
[ResTerm] [varchar](50) NULL,
[Mobile] [varchar](50) NULL,
[Tel] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[ShuttleAddress2] [varchar](100) NULL,
[ShuttleAddress1] [varchar](100) NULL,
[VisitTime] [varchar](50) NULL,
[ArrangeClass] [varchar](50) NULL,
[BusinessWay] [varchar](50) NULL,
[BusinessType] [varchar](50) NULL,
[BusinessSources2] [varchar](50) NULL,
[BusinessSources3] [varchar](50) NULL,
[CoachDesEmpID] [int] NULL,
[CoachDesEmpName] [varchar](50) NULL,
[StuCoachEmpID] [int] NULL,
[StuCoachEmpName] [varchar](50) NULL,
[Class] [varchar](50) NULL,
[ClassName] [varchar](4) NOT NULL,
[ApplyType] [varchar](10) NULL,
[CarType] [varchar](50) NOT NULL,
[TransferType] [int] NULL,
[MarketType] [varchar](50) NULL,
[MarketContent] [varchar](100) NULL,
[Gifts] [varchar](50) NULL,
[OfferWay] [varchar](50) NULL,
[CouponNo] [varchar](50) NULL,
[CoType] [varchar](50) NULL,
[IsGroup] [int] NOT NULL,
[Remark] [varchar](2000) NULL,
[EntryDate] [datetime] NOT NULL,
[DjfNum] [varchar](50) NULL,
[AreaOrganID] [int] NOT NULL,
[AreaPointCode] [varchar](50) NOT NULL,
[DeptOrganID] [int] NOT NULL,
[DeptPointCode] [varchar](50) NOT NULL,
[ConEmpID] [int] NOT NULL,
[ConEmpName] [varchar](50) NOT NULL,
[ConEmpTel] [varchar](50) NULL,
[InDate] [datetime] NOT NULL,
[AddEmpID] [int] NOT NULL,
[AddEmpName] [varchar](50) NOT NULL,
[OrganID] [int] NOT NULL,
[JlClassName] [varchar](50) NULL,
[IsDel] [int] NOT NULL,
[AuState] [int] NOT NULL,
[AuEmpID] [int] NULL,
[AuTime] [datetime] NULL,
[IsInternal] [int] NOT NULL,
[StuInTime] [datetime] NULL,
[SLRQ] [datetime] NULL,
[LSH] [int] NULL,
[StuType] [int] NOT NULL,
[PrioCoachID] [int] NULL,
[PrioCoachName] [varchar](50) NULL
)
--自增
CREATE TABLE #Studentid2
(
ID INT IDENTITY(1,1),
[StuID] [int] NOT NULL,
[StuNum] [varchar](50) NOT NULL,
[StuNamePY] [varchar](50) NOT NULL,
[StuNameWB] [varchar](50) NOT NULL,
[StuName] [varchar](50) NOT NULL,
[IDNumber] [varchar](18) NOT NULL,
[FileNo] [varchar](50) NULL,
[DocType] [varchar](50) NULL,
[Birthday] [varchar](10) NOT NULL,
[Sex] [int] NOT NULL,
[Height] [int] NULL,
[DocAddress] [varchar](200) NULL,
[Birthplace] [varchar](50) NULL,
[ISGOT] [int] NOT NULL,
[Address] [varchar](200) NULL,
[ResTerm] [varchar](50) NULL,
[Mobile] [varchar](50) NULL,
[Tel] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[ShuttleAddress2] [varchar](100) NULL,
[ShuttleAddress1] [varchar](100) NULL,
[VisitTime] [varchar](50) NULL,
[ArrangeClass] [varchar](50) NULL,
[BusinessWay] [varchar](50) NULL,
[BusinessType] [varchar](50) NULL,
[BusinessSources2] [varchar](50) NULL,
[BusinessSources3] [varchar](50) NULL,
[CoachDesEmpID] [int] NULL,
[CoachDesEmpName] [varchar](50) NULL,
[StuCoachEmpID] [int] NULL,
[StuCoachEmpName] [varchar](50) NULL,
[Class] [varchar](50) NULL,
[ClassName] [varchar](4) NOT NULL,
[ApplyType] [varchar](10) NULL,
[CarType] [varchar](50) NOT NULL,
[TransferType] [int] NULL,
[MarketType] [varchar](50) NULL,
[MarketContent] [varchar](100) NULL,
[Gifts] [varchar](50) NULL,
[OfferWay] [varchar](50) NULL,
[CouponNo] [varchar](50) NULL,
[CoType] [varchar](50) NULL,
[IsGroup] [int] NOT NULL,
[Remark] [varchar](2000) NULL,
[EntryDate] [datetime] NOT NULL,
[DjfNum] [varchar](50) NULL,
[AreaOrganID] [int] NOT NULL,
[AreaPointCode] [varchar](50) NOT NULL,
[DeptOrganID] [int] NOT NULL,
[DeptPointCode] [varchar](50) NOT NULL,
[ConEmpID] [int] NOT NULL,
[ConEmpName] [varchar](50) NOT NULL,
[ConEmpTel] [varchar](50) NULL,
[InDate] [datetime] NOT NULL,
[AddEmpID] [int] NOT NULL,
[AddEmpName] [varchar](50) NOT NULL,
[OrganID] [int] NOT NULL,
[JlClassName] [varchar](50) NULL,
[IsDel] [int] NOT NULL,
[AuState] [int] NOT NULL,
[AuEmpID] [int] NULL,
[AuTime] [datetime] NULL,
[IsInternal] [int] NOT NULL,
[StuInTime] [datetime] NULL,
[SLRQ] [datetime] NULL,
[LSH] [int] NULL,
[StuType] [int] NOT NULL,
[PrioCoachID] [int] NULL,
[PrioCoachName] [varchar](50) NULL
)
--列集
CREATE TABLE #Studentid3
(
[StuID] [int] NULL,
[StuNum] [varchar](50) SPARSE NULL,
[StuNamePY] [varchar](50) SPARSE NULL,
[StuNameWB] [varchar](50) SPARSE NULL,
[StuName] [varchar](50) SPARSE NULL,
[IDNumber] [varchar](18) SPARSE NULL,
[FileNo] [varchar](50) SPARSE NULL,
[DocType] [varchar](50) SPARSE NULL,
[Birthday] [varchar](10) SPARSE NULL,
[Sex] [int] SPARSE NULL,
[Height] [int] SPARSE NULL,
[DocAddress] [varchar](200) SPARSE NULL,
[Birthplace] [varchar](50) SPARSE NULL,
[ISGOT] [int] SPARSE NULL,
[Address] [varchar](200) SPARSE NULL,
[ResTerm] [varchar](50) SPARSE NULL,
[Mobile] [varchar](50) SPARSE NULL,
[Tel] [varchar](50) SPARSE NULL,
[Email] [varchar](50) SPARSE NULL,
[ShuttleAddress2] [varchar](100) SPARSE NULL,
[ShuttleAddress1] [varchar](100) SPARSE NULL,
[VisitTime] [varchar](50) SPARSE NULL,
[ArrangeClass] [varchar](50) SPARSE NULL,
[BusinessWay] [varchar](50) SPARSE NULL,
[BusinessType] [varchar](50) SPARSE NULL,
[BusinessSources2] [varchar](50) SPARSE NULL,
[BusinessSources3] [varchar](50) SPARSE NULL,
[CoachDesEmpID] [int] SPARSE NULL,
[CoachDesEmpName] [varchar](50) SPARSE NULL,
[StuCoachEmpID] [int] SPARSE NULL,
[StuCoachEmpName] [varchar](50) SPARSE NULL,
[Class] [varchar](50) SPARSE NULL,
[ClassName] [varchar](4) SPARSE NULL,
[ApplyType] [varchar](10) SPARSE NULL,
[CarType] [varchar](50) SPARSE NULL,
[TransferType] [int] SPARSE NULL,
[MarketType] [varchar](50) SPARSE NULL,
[MarketContent] [varchar](100) SPARSE NULL,
[Gifts] [varchar](50) SPARSE NULL,
[OfferWay] [varchar](50) SPARSE NULL,
[CouponNo] [varchar](50) SPARSE NULL,
[CoType] [varchar](50) SPARSE NULL,
[IsGroup] [int] SPARSE NULL,
[Remark] [varchar](2000) SPARSE NULL,
[EntryDate] [datetime] SPARSE NULL,
[DjfNum] [varchar](50) SPARSE NULL,
[AreaOrganID] [int] NULL,
[AreaPointCode] [varchar](50) SPARSE NULL,
[DeptOrganID] [int] SPARSE NULL,
[DeptPointCode] [varchar](50) SPARSE NULL,
[ConEmpID] [int] SPARSE NULL,
[ConEmpName] [varchar](50) SPARSE NULL,
[ConEmpTel] [varchar](50) SPARSE NULL,
[InDate] [datetime] SPARSE NULL,
[AddEmpID] [int] SPARSE NULL,
[AddEmpName] [varchar](50) SPARSE NULL,
[OrganID] [int] SPARSE NULL,
[JlClassName] [varchar](50) SPARSE NULL,
[IsDel] [int] SPARSE NULL,
[AuState] [int] SPARSE NULL,
[AuEmpID] [int] SPARSE NULL,
[AuTime] [datetime] SPARSE NULL,
[IsInternal] [int] SPARSE NULL,
[StuInTime] [datetime] SPARSE NULL,
[SLRQ] [datetime] SPARSE NULL,
[LSH] [int] SPARSE NULL,
[StuType] [int] SPARSE NULL,
[PrioCoachID] [int] SPARSE NULL,
[PrioCoachName] [varchar](50) SPARSE NULL,
SpecialPurposeColumns XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
)
declare @t1 int
declare @t2 int
declare @t3 int
declare @t4 int
declare @t5 int
DECLARE @d DATETIME
SET @d=GETDATE()
--Script.Start
insert #Studentid
select TOP (@TOP) *
from SGPZ..student
--Script.End
set @t1=DATEDIFF(ms,@d,GETDATE())
DECLARE @d1 DATETIME
SET @d1=GETDATE()
insert into #studentid1 ([StuName],[StuID],[StuNum],[StuNamePY],
[StuNameWB],[IDNumber],[FileNo],[DocType],
[Birthday],[Sex],[Height],[DocAddress],
[Birthplace],[ISGOT],[Address],[ResTerm],[Mobile],
[Tel],[Email],[ShuttleAddress2],[ShuttleAddress1],
[VisitTime],[ArrangeClass],[BusinessWay],
[BusinessType],[BusinessSources2],
[BusinessSources3],[CoachDesEmpID],
[CoachDesEmpName],[StuCoachEmpID],
[StuCoachEmpName],[Class],[ClassName],[ApplyType],
[CarType],[TransferType],[MarketType],
[MarketContent],[Gifts],[OfferWay],[CouponNo],
[CoType],[IsGroup],[Remark],[EntryDate],[DjfNum],
[AreaOrganID],[AreaPointCode],[DeptOrganID],
[DeptPointCode],[ConEmpID],[ConEmpName],
[ConEmpTel],[InDate],[AddEmpID],[AddEmpName],
[OrganID],[JlClassName],[IsDel],[AuState],
[AuEmpID],[AuTime],[IsInternal],[StuInTime],
[SLRQ],[LSH],[StuType],[PrioCoachID],
[PrioCoachName])
select TOP (@TOP) [StuName],[StuID],[StuNum],[StuNamePY],[StuNameWB],
[IDNumber],[FileNo],[DocType],[Birthday],[Sex],[Height],
[DocAddress],[Birthplace],[ISGOT],[Address],[ResTerm],[Mobile],
[Tel],[Email],[ShuttleAddress2],[ShuttleAddress1],[VisitTime],
[ArrangeClass],[BusinessWay],[BusinessType],[BusinessSources2],
[BusinessSources3],[CoachDesEmpID],[CoachDesEmpName],
[StuCoachEmpID],[StuCoachEmpName],[Class],[ClassName],[ApplyType],
[CarType],[TransferType],[MarketType],[MarketContent],[Gifts],
[OfferWay],[CouponNo],[CoType],[IsGroup],[Remark],[EntryDate],
[DjfNum],[AreaOrganID],[AreaPointCode],[DeptOrganID],
[DeptPointCode],[ConEmpID],[ConEmpName],[ConEmpTel],[InDate],
[AddEmpID],[AddEmpName],[OrganID],[JlClassName],[IsDel],[AuState],
[AuEmpID],[AuTime],[IsInternal],[StuInTime],[SLRQ],[LSH],[StuType],
[PrioCoachID],[PrioCoachName]
from SGPZ..student
set @t2=DATEDIFF(ms,@d1,GETDATE())
DECLARE @d2 DATETIME
SET @d2=GETDATE()
insert into #studentid2 ([StuName],[StuID],[StuNum],[StuNamePY],
[StuNameWB],[IDNumber],[FileNo],[DocType],
[Birthday],[Sex],[Height],[DocAddress],
[Birthplace],[ISGOT],[Address],[ResTerm],[Mobile],
[Tel],[Email],[ShuttleAddress2],[ShuttleAddress1],
[VisitTime],[ArrangeClass],[BusinessWay],
[BusinessType],[BusinessSources2],
[BusinessSources3],[CoachDesEmpID],
[CoachDesEmpName],[StuCoachEmpID],
[StuCoachEmpName],[Class],[ClassName],[ApplyType],
[CarType],[TransferType],[MarketType],
[MarketContent],[Gifts],[OfferWay],[CouponNo],
[CoType],[IsGroup],[Remark],[EntryDate],[DjfNum],
[AreaOrganID],[AreaPointCode],[DeptOrganID],
[DeptPointCode],[ConEmpID],[ConEmpName],
[ConEmpTel],[InDate],[AddEmpID],[AddEmpName],
[OrganID],[JlClassName],[IsDel],[AuState],
[AuEmpID],[AuTime],[IsInternal],[StuInTime],
[SLRQ],[LSH],[StuType],[PrioCoachID],
[PrioCoachName])
select TOP (@TOP) [StuName],[StuID],[StuNum],[StuNamePY],[StuNameWB],
[IDNumber],[FileNo],[DocType],[Birthday],[Sex],[Height],
[DocAddress],[Birthplace],[ISGOT],[Address],[ResTerm],[Mobile],
[Tel],[Email],[ShuttleAddress2],[ShuttleAddress1],[VisitTime],
[ArrangeClass],[BusinessWay],[BusinessType],[BusinessSources2],
[BusinessSources3],[CoachDesEmpID],[CoachDesEmpName],
[StuCoachEmpID],[StuCoachEmpName],[Class],[ClassName],[ApplyType],
[CarType],[TransferType],[MarketType],[MarketContent],[Gifts],
[OfferWay],[CouponNo],[CoType],[IsGroup],[Remark],[EntryDate],
[DjfNum],[AreaOrganID],[AreaPointCode],[DeptOrganID],
[DeptPointCode],[ConEmpID],[ConEmpName],[ConEmpTel],[InDate],
[AddEmpID],[AddEmpName],[OrganID],[JlClassName],[IsDel],[AuState],
[AuEmpID],[AuTime],[IsInternal],[StuInTime],[SLRQ],[LSH],[StuType],
[PrioCoachID],[PrioCoachName]
from SGPZ..student
set @t3=DATEDIFF(ms,@d2,GETDATE())
DECLARE @d3 DATETIME
SET @d3=GETDATE()
insert into #studentid3 ([StuName],[StuID],[StuNum],[StuNamePY],
[StuNameWB],[IDNumber],[FileNo],[DocType],
[Birthday],[Sex],[Height],[DocAddress],
[Birthplace],[ISGOT],[Address],[ResTerm],[Mobile],
[Tel],[Email],[ShuttleAddress2],[ShuttleAddress1],
[VisitTime],[ArrangeClass],[BusinessWay],
[BusinessType],[BusinessSources2],
[BusinessSources3],[CoachDesEmpID],
[CoachDesEmpName],[StuCoachEmpID],
[StuCoachEmpName],[Class],[ClassName],[ApplyType],
[CarType],[TransferType],[MarketType],
[MarketContent],[Gifts],[OfferWay],[CouponNo],
[CoType],[IsGroup],[Remark],[EntryDate],[DjfNum],
[AreaOrganID],[AreaPointCode],[DeptOrganID],
[DeptPointCode],[ConEmpID],[ConEmpName],
[ConEmpTel],[InDate],[AddEmpID],[AddEmpName],
[OrganID],[JlClassName],[IsDel],[AuState],
[AuEmpID],[AuTime],[IsInternal],[StuInTime],
[SLRQ],[LSH],[StuType],[PrioCoachID],
[PrioCoachName])
select TOP (@TOP) [StuName],[StuID],[StuNum],[StuNamePY],[StuNameWB],
[IDNumber],[FileNo],[DocType],[Birthday],[Sex],[Height],
[DocAddress],[Birthplace],[ISGOT],[Address],[ResTerm],[Mobile],
[Tel],[Email],[ShuttleAddress2],[ShuttleAddress1],[VisitTime],
[ArrangeClass],[BusinessWay],[BusinessType],[BusinessSources2],
[BusinessSources3],[CoachDesEmpID],[CoachDesEmpName],
[StuCoachEmpID],[StuCoachEmpName],[Class],[ClassName],[ApplyType],
[CarType],[TransferType],[MarketType],[MarketContent],[Gifts],
[OfferWay],[CouponNo],[CoType],[IsGroup],[Remark],[EntryDate],
[DjfNum],[AreaOrganID],[AreaPointCode],[DeptOrganID],
[DeptPointCode],[ConEmpID],[ConEmpName],[ConEmpTel],[InDate],
[AddEmpID],[AddEmpName],[OrganID],[JlClassName],[IsDel],[AuState],
[AuEmpID],[AuTime],[IsInternal],[StuInTime],[SLRQ],[LSH],[StuType],
[PrioCoachID],[PrioCoachName]
from SGPZ..student
set @t4=DATEDIFF(ms,@d3,GETDATE())
DECLARE @d4 DATETIME
SET @d4=GETDATE()
select TOP (@TOP) *
INTO #studentid4
from SGPZ..student
set @t5=DATEDIFF(ms,@d4,GETDATE())
insert #result
select @TOP,@t1,@t2,@t3,@t4,@t5
set @i=@i+1
end
-------------------------------------------------------------------------------------------------------------------------
select *
from #result
---------------------------
插入行数 正常临时表 新加GUID列的临时表 新加自增列临时表 列集 into方式创建临时表
10365 1306 160 510 1090 570
66620 3343 1006 1040 1466 886
15032 306 233 236 323 233
13319 183 186 190 290 150
7910 110 113 116 173 183
121664 4213 1736 2026 2626 1313
107292 1473 1530 1590 2396 1123
137653 1863 1983 2146 2913 1496
132387 1833 1860 1983 2813 1373
71988 986 1033 1083 1523 776
65327 873 913 950 1380 653
86183 1160 1213 1266 1836 836
31515 423 443 463 673 310
115917 1570 1623 1713 2473 1406
60640 840 850 890 1300 616
125587 1690 1790 1830 2680 1233
111565 1513 1586 1676 2393 1093
78163 1183 1373 1373 1720 833
133448 1843 2000 2093 3110 1626
39159 630 640 650 950 443
114811 1806 1963 1960 2680 1393
136738 1843 1920 1970 3313 1650
140290 2093 2096 2170 3080 1373
83710 1173 1186 1220 1796 813
119543 1856 1936 1783 2543 1206
103006 1410 1453 1516 2230 1026
109087 1520 1546 1586 2293 1073
68457 916 963 980 1440 740
68661 930 970 1016 1466 1003
77162 1410 1496 1546 2260 1000
133915 2490 2500 2640 3783 1856
