Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Variable table and temporary table in SQL

2025-03-31 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report