SQL Server, temporary tables with truncate vs table variable with delete

Posted by Richard on Stack Overflow See other posts from Stack Overflow or by Richard
Published on 2010-04-15T15:32:50Z Indexed on 2010/04/15 15:43 UTC
Read the original article Hit count: 491

Filed under:

sqlserver2005

|

temporary-tables

I have a stored procedure inside which I create a temporary table that typically contains between 1 and 10 rows. This table is truncated and filled many times during the stored procedure. It is truncated as this is faster than delete. Do I get any performance increase by replacing this temporary table with a table variable when I suffer a penalty for using delete (truncate does not work on table variables)

Whilst table variables are mainly in memory and are generally faster than temp tables do I loose any benefit by having to delete rather than truncate?

© Stack Overflow or respective owner

Related posts about sqlserver2005

SqlServer2005 Enterprise Fast Recovery, SqlAgent Availability, and Replication

as seen on Server Fault - Search for 'Server Fault'
I have a database under SqlServer2005 Enterprise 64bit sp3, that is in phase 3 of 3 of recovery after a reboot without normal shutdown. It looks like with fast Recovery, the database became available when recovery moved into phase 3. However, it seems (based on message in SqlAgent log), that SqlAgent… >>> More
"select * from table" vs "select colA,colB,etc from table" interesting behaviour in SqlServer2005

as seen on Stack Overflow - Search for 'Stack Overflow'
Apology for a lengthy post but I needed to post some code to illustrate the problem. Inspired by the question What is the reason not to use select * ? posted a few minutes ago, I decided to point out some observations of the select * behaviour that I noticed some time ago. So let's the code speak… >>> More
deleting records from multiple tables at a time with a single query in sqlserver2005

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi I wanna delete records from child tables as well as parent table with in a single query. please find the query given below. here response header is the primary table and responseid is the primary key. DELETE FROM responseheader FROM responseheader INNER JOIN responsepromotion ON responseheader… >>> More
My log file is so big, after truncate it 2 days, it still big like before

as seen on Server Fault - Search for 'Server Fault'
I'm using sql server 2005 and the log is too big 23 gbs (if you compare with the data file only 8GB). I've already run the truncate, after that the log file is 24mb but after 2 days, it becomes 23gbs. My database is full backup mode. I have the maintain plan is 2 hours will have diff backup and 24hours… >>> More
Facing problem in configuring Reporting Server

as seen on Server Fault - Search for 'Server Fault'
Dear All, I am unable to configure reporting server with sql server 2005 express edition. I have posted the link of a screen shot which shows the status.when ever i go to configure the reporting services it gives me the following errors(see screen shot)...also unable to start the reporting services… >>> More

Related posts about temporary-tables

SQL Server, temporary tables with truncate vs table variable with delete

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a stored procedure inside which I create a temporary table that typically contains between 1 and 10 rows. This table is truncated and filled many times during the stored procedure. It is truncated as this is faster than delete. Do I get any performance increase by replacing this temporary… >>> More
Entity Framework, full-text search and temporary tables

as seen on Stack Overflow - Search for 'Stack Overflow'
I have a LINQ-2-Entity query builder, nesting different kinds of Where clauses depending on a fairly complex search form. Works great so far. Now I need to use a SQL Server fulltext search index in some of my queries. Is there any chance to add the search term directly to the LINQ query, and have… >>> More
PHP, MySQL, and temporary tables

as seen on Stack Overflow - Search for 'Stack Overflow'
Php novice. 1.Is there anything wrong with this PHP & MySQL code? include_once "db_login.php" ; $sql = "DROP TEMPORARY TABLE IF EXISTS temp_sap_id_select" ; mysql_query ( $sql ) or ( "Error " . mysql_error () ) ; $sql = " CREATE TEMPORARY TABLE temp_sap_id_select ( … >>> More
Comparing Table Variables with Temporary Tables

as seen on SQL Server Central - Search for 'SQL Server Central'
This articles brings a comparison of temporary tables with table variables from SQL Server author, Wayne Sheffield. In includes an in-depth look at the differences between them. SQL Server monitoring made easy "Keeping an eye on our many SQL Server instances is much easier… >>> More
oracle global temporary tables

as seen on Stack Overflow - Search for 'Stack Overflow'
I created the global temp table. when I execute the code as an individual scripts it works fine. but when I execute it as a single script in TOAD then no record was created. there was just an empty global temp table. eg. CREATE GLOBAL TEMPORARY TABLE TEMP_TRAN ( COL1 NUMBER(9), COL2 VARCHAR2(30)… >>> More