Prev: Dynamic Pivot
Next: Newsletter System
From: tshad on 19 Apr 2010 17:44
I was looking at an article that says:
********************************
A side note: Using transient permanent tables for temporary data is never a
good idea. Permanent tables require writes to the transaction log, which
incur a performance hit.
***********************************

Don't inserts, updates and deletes to temp tables access the log as well?

Thanks,

Tom


From: Erland Sommarskog on 19 Apr 2010 17:58
tshad (t(a)dslextreme.com) writes:
> I was looking at an article that says:
> ********************************
> A side note: Using transient permanent tables for temporary data is
> never a good idea. Permanent tables require writes to the transaction
> log, which incur a performance hit.
> ***********************************
>
> Don't inserts, updates and deletes to temp tables access the log as well?

Yes, temp tables are logged to. As are table variables. But objects in
tempdb are less logged than in user datadatabases, since tempdb tables
never have to be rolled forwared, only rolled back. (Roll forward happens
on startup, when there committed transactions that have not yet been written
to the target tables.)


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Dynamic Pivot
Next: Newsletter System