Comparison and analysis of various temporary tables and Efficiency Comparison

Comparison and analysis of various temporary tables and Efficiency Comparison
Comparison and analysis of various temporary tablesOld Shuai (20141107)When writing a stored procedure, temporary tables are often used. What kind of efficiency is better for the use of temporary tables? Let's make a comparative analysis.Experiment Data: Table a has 4 million records and only queries one field Title.
1. Direct Query-- Enable Time AnalysisSET STATISTICS TIME ON
GO-- QuerySELECT Title FROM-- ResultSQL Server execution time: Occupied time = 22013 milliseconds.
2. Variable table-- Enable Time AnalysisSET STATISTICS TIME ON
GO-- InsertDECLARE @ tmpA TABLE (Title VARCHAR (200) insert into @ tmpASELECT Title FROM-- QuerySELECT * FROM @ tmpA-- ResultSQL Server execution time:Time used = 22913 milliseconds.
3. Temporary table-- Enable Time AnalysisSET STATISTICS TIME ON
GO-- InsertCreate table # tmpA (Title VARCHAR (200) insert into # tmpASELECT Title FROM-- QuerySELECT * FROM # tmpA-- ResultSQL Server execution time:Time used = 22743 milliseconds.
4. Do not create a temporary table and insert it directly to the temporary table.-- Enable Time AnalysisSET STATISTICS TIME ON
GO-- InsertSELECT * INTO # tmpA FROM (SELECT Title FROM a) AS B-- QuerySELECT * FROM # tmpA-- ResultSQL Server execution time:Time used = 22671 milliseconds.
5. Use the with as temporary table-- Enable Time AnalysisSet statistics time on;-- InsertWITH tmpA AS (SELECT Title FROM)-- QuerySELECT * FROM tmpA-- ResultSQL Server execution time:Time used = 22188 milliseconds.
Through the above analysis, it is easy to see which method is more efficient. Try to use a temporary table to store temporary data during the storage process. Do not use a variable table.

I wrote an SQL statement and ran it for 2 minutes without using a temporary table. It took less than 1 second to use a temporary table. I want to know how the temporary table improves the execution efficiency?

I have never learned computer principles, just like cache,
Temporary tables do not actually store data, but temporarily allocate a space and execute it directly.

Using temporary tables or creating views in SQL is more efficient.

1. Method of existence:
Temporarily stored in server memory
No view form
2. lifecycle:
When the SQL Service of the temporary table is disabled, it disappears.
View will not disappear without deleting it
3. Purpose
Temporary tables are often used as intermediate transfer layers.
View as the window of the physical table
4. Efficiency
The temporary table is cached, so the execution efficiency is relatively high.
View efficiency is average, but it saves I/O operations and resources
5. When using stored procedures:
Temporary table, high efficiency {it is possible that the amount of data is small, and the temporary table is in the cache, so the execution efficiency is high}
General View