CODEWITHSUNDEEP
sqlsql-server
Ludovic Aubert
Ludovic Aubert

Reputation: 10526

update speed temp table versus table variable

Update of temp table is slowed down by logging on update. Will the same logging happen on a table variable ? Is there a way to prevent this logging to speed up updates on a large table. For exemple in an ETL, you dont need rollback capability.

Upvotes: 0

Views: 113

Answers (1)

PeterHe
PeterHe

Reputation: 2766

Trx logs are not applied to table variables, and also no statistics generated for table variables. So it depends on how you use the table variables whether they perform better or not than temp tables. And NO, you can't disable trx logging for tables or temp tables in SQL server. You can change database option to BULK Logged for better logging performance, however it will affect all the tables in the db and it has some implications for the db recovery. Unless the db is purely for ETL, it is not recommended.

Upvotes: 1

Related Questions