Under what is the table variable?
Under the circumstances?
-------------------------------------------------- -------------
The table variable is only stored in memory, and the temporary table needs to write the disk. So in general, the use of table variables will be fast.
The usage is not quite the same, and sometimes it is more convenient to use the table variable.
The specific help of the SQL 2000 will be seen. I will know when trying.
-------------------------------------------------- -------------
personal opinion:
1, the table variable is default in memory, fast, so in the trigger, if the data is not large, the data should be used.
2. Temporary table defaults to use hard drives, in general, the speed is slow, is it not a temporary table? Nor, when the amount of data is large, if you use the table variable, you will consume the memory, then use the Tempdb space, which mainly uses the hard disk space, but at the same time, the memory is basically exhausted, and the memory transfer is added. Opportunity, reducing speed. This situation is recommended to assign a suitable space once a time, then use a temporary table.
-------------------------------------------------- -------------
Skin is understood:
Table variable: need to know the table structure in advance
Ordinary Temporary Table: You can use the same INTO as the table variable in the current session, convenient
Global Temporary Table: Use the DROP that exists in Temp in multiple sessions
-------------------------------------------------- -------------
To support the scope of the table variable, support the unsupported operation, the size of the machine memory is considered.
Such as:
The table variable is equivalent to the ADO's Recordset, which is much faster than the temporary table.
The table variable cannot be used in the following statement:
INSERT INTO TABLE_VARIABLE EXEC stored procedure.
SELECT SELECT_LIST INTO TABLE_VARIABLE statement.
The Table variable is automatically cleared when the function, stored procedure or batch of Table variables is defined.
But temporary table support.
The table variable speed is much faster than the temporary table (if the memory is sufficient)
If the amount of data is not large:
Microsoft Book on Line said: Use the table variables as possible without using a temporary table.
-------------------------------------------------- -------------
Pasket out to see:
Use table variables as much as possible without using a temporary table. Table variables have the following advantages:
The behavior of the Table variable is similar to a local variable, which has a clear defined scope. This scope is a function, stored procedure or batch of the variable.
In its scope, Table variables can be used like conventional tables. This variable can be applied to the expressions of the table or table in Select, Insert, Update, and Delete statements. However, Table cannot be used in the following statements:
INSERT INTO TABLE_VARIABLE EXEC stored procedure.
SELECT SELECT_LIST INTO TABLE_VARIABLE statement.
The Table variable is automatically cleared when the function, stored procedure or batch of Table variables is defined.
The use of table variables during the stored procedure reduces the recipreciation amount of the stored procedure than using a temporary table.
Transactions involving table variables are only present during the table variable update. This reduces the need for table variables to lock and recording resources.
Do not support assignment operations between table variables. In addition, since the table variable scope is limited, and is not part of a persistent database, it is not affected by the transaction rollback.
-------------------------------------------------- -------------
In my opinion:
1. Table variables are in memory, and the temporary table is placed on the hard disk;
2. Use a temporary table to consider the problem of locking locks;
3. The amount of data should be used to use a temporary table.
