What is the difference between a Temporary Table and a Table Variable?
1. Table variable is created in the memory where as a temporary table is created in the TempDB. But, if there is a memory pressure, the pages belonging to a table variable may be pushed out to tempdb.
2. Table variables cannot be involved in transactions, logging or locking. This makes table variable faster than a temporary table.
3. You can pass table variable as parameter to functions and stored procedures, where as you cannot do the same with temporary table.
4. A temporary table can have indexes, whereas a table variable can only have a primary index. If speed is an issue Table variables can be faster, but if there are a lot of records, or there is a need to search the temporary table based on a clustered index, then a Temporary Table would be better. If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won't create statistics on table variables.
Table Variable
Temp Table
1. Table variable is created in the memory where as a temporary table is created in the TempDB. But, if there is a memory pressure, the pages belonging to a table variable may be pushed out to tempdb.
2. Table variables cannot be involved in transactions, logging or locking. This makes table variable faster than a temporary table.
3. You can pass table variable as parameter to functions and stored procedures, where as you cannot do the same with temporary table.
4. A temporary table can have indexes, whereas a table variable can only have a primary index. If speed is an issue Table variables can be faster, but if there are a lot of records, or there is a need to search the temporary table based on a clustered index, then a Temporary Table would be better. If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won't create statistics on table variables.
Table Variable
Temp Table
No comments:
Post a Comment