Variable table and temporary table in SQL

1. Variable table:

Declare @ SDT datetime,@EDT datetime-define execution start and end time set @ SDT=getdate ()-define variable table declare @ t table (ID int,Myfield nvarchar (50), InputDT datetime)-insert data into variable table insert @ t select top 10000 ID,Myfield,getdate () from table set @ EDT=getdate () select DATEDIFF (ms,@SDT,@EDT) AS Diffms-start and end time interval

2. Temporary table

Declare @ SDT datetime,@EDT datetimeset @ SDT=getdate ()-create temporary table: create table # t (ID int,Myfield nvarchar (50), InputDT datetime) insert # t select top 10000 ID,Myfield,getdate () from table select * from # tset @ EDT=getdate () select DATEDIFF (ms,@SDT,@EDT) AS DiffNSdrop table # t

Insert directly without creating a temporary table

Declare @ SDT datetime,@EDT datetimeset @ SDT=getdate () select top 10000 ID,Myfield,getdate () into # t from Table select * from # tset @ EDT=getdate () select DATEDIFF (ms,@SDT,@EDT) AS DiffNSdrop table # t

Summary: when the amount of data is small [the total number of rows is less than 1000], use the variable table

When the amount of data is large (the number of rows > 100000), use to create a temporary table and then insert it.

When the amount of data is general (100000 > rows > 10, 000), it is inserted directly without establishing a temporary table.

The results of the above tests may vary from machine to machine.