In SQL, must table variables replace temporary tables?

Http://database.51cto.com anonymous name Internet I want to comment ( 0 )

• Abstract:Must table variables be used in SQL databases to replace temporary tables? The number of rows inserted into the table, the number of recompilation times for saving the query from the table, and the query type and their dependence on performance index and statistical information are three important factors that determine whether the table is established..
• Tags:SQL table Variables

Must table variables be used in SQL databases to replace temporary tables? The answer depends on three factors: the number of rows inserted into the table, the number of times the query is recompiled from the table, and the query type and its dependence on performance indexes and statistical information.

In some cases, you can split a stored procedure with a temporary table into multiple smaller stored procedures for recompilation on smaller units.

Table variables should be used whenever possible unless the data volume is large and the table needs to be reused. In this case, you can create an index on a temporary table to improve query performance. However, different solutions may be different. Microsoft recommends that you perform a test to verify whether table variables are more effective for specific queries or stored procedures than temporary tables.

======================================

Declare @ temp table
(
[ID] int identity (1, 1 ),
[Name] varchar (10)
)
Declare @ tempid int, @ tempname varchar (10)

Insert into @ temp values ('A ')
Insert into @ temp values ('B ')
Insert into @ temp values ('C ')
Insert into @ temp values ('D ')
Insert into @ temp values ('E ')

-- Select * From @ temp

While exists (select [ID] From @ temp)
Begin
Set rowcount 1
Select @ tempid = [ID], @ tempname = [name] From @ temp
Set rowcount 0
Delete from @ temp where [ID] = @ tempid

Print 'name: ---- '+ @ tempname
End

[Edit recommendations]

Insufficient table variables in SQL

Explain how to use the Loop Structure in SQL

SQL loop execution while Control

Examples of nested cursors in SQL

SQL loop statement example