The use of temporary table objects (not table variables) including semantics, design, performance optimization, and indexing. See also [table-variable].
Questions tagged [temporary-tables]
280 questions
480
votes
2 answers
What's the difference between a temp table and table variable in SQL Server?
This seems to be an area with quite a few myths and conflicting views.
So what is the difference between a table variable and a local temporary table in SQL Server?
Martin Smith
- 80,333
- 15
- 230
- 323
37
votes
2 answers
Is it generally faster to select into a temp table than selecting into an actual table?
I thought I once read somewhere that writing to tempdb is faster than an actual table not in tempdb. Is this true in any capacity?
I thought I recall it saying something special about tempdb and storing the data in memory?
J.D.
- 30,123
- 7
- 45
- 96
37
votes
2 answers
Why is using a table variable more than twice as fast as a #temp table in this specific case?
I was looking at the article here
Temporary Tables vs. Table Variables and Their Effect on SQL Server Performance and on SQL Server 2008 was able to reproduce similar results to those shown there for 2005.
When executing the stored procedures…
Martin Smith
- 80,333
- 15
- 230
- 323
32
votes
3 answers
MySQL creates temporary tables on disk. How do I stop it?
We are running a site (Moodle) that the users currently find slow. I think I have tracked down the problem to MySQL creating temporary tables on disk. I watch the variable created_tmp_disk_tables in Mysql Workbench server administration and the…
user30431
- 453
- 1
- 5
- 11
25
votes
1 answer
Insert results from a stored procedure into a table variable
I have a stored procedure that stores values in a table variable. I select these values and return them when the procedure is called.
I am trying to set these return values in another table variable but I can't figure it out.
Stored procedure
ALTER…
ThunD3eR
- 387
- 1
- 3
- 8
18
votes
2 answers
"Copying to tmp table" extremely slow
This is my example of query:
SELECT
nickname,
CASE class_id
WHEN 1 THEN 'Druid'
WHEN 2 THEN 'Necromancer'
WHEN 3 THEN 'Mage'
WHEN 4 THEN 'Priest'
WHEN 5 THEN 'Warrior'
WHEN 6 THEN 'Stalker'
WHEN 7…
Gofrolist
- 181
- 1
- 1
- 3
16
votes
2 answers
Does varchar size matter in temporary tables?
There is a debate at my wife's work about just using varchar(255) for all varchar fields in temporary tables in stored procedures. Basically, one camp wants to use 255 because it will always work even if the definition changes, and the other camp…
Brian Nickel
- 263
- 2
- 7
13
votes
3 answers
How long will a temporary MEMORY table persist if I don't drop it (MySQL)
I'm using a recursive stored procedure in MySQL to generate a temporary table called id_list, but I must use the results of that procedure in a follow up select query, so I can't DROP the temporary table within the procedure...
BEGIN;
/* generates…
oucil
- 506
- 2
- 7
- 18
13
votes
1 answer
SQL Server table name starting with # in user database, not in tempdb, not a temp table
Somehow, decades ago, a table got created in our database that starts with a #. It shows up in Object Explorer under the app's database, not in tempdb. For some reason, Azure won't import the database like this.
We can't drop it, rename it, or…
that it guy
- 181
- 9
13
votes
1 answer
Why does truncating a temp table at the end of the stored procedure that creates it free tempdb space faster?
SQL Server caches temp tables created within stored procedures and merely renames them when the procedure ends and is subsequently executed. My question has to do with when the tempdb space is released. I've read that the table is truncated at the…
Mark Freeman
- 2,130
- 5
- 27
- 50
13
votes
2 answers
INSERT performance difference between temporary tables and table variable
I have the following issue in SQL Server 2005: trying to inserts some rows into a table variable takes a lot of time compared to the same insert using a temporary table.
This is the code to insert into the table variable
DECLARE @Data…
munissor
- 253
- 1
- 2
- 8
12
votes
2 answers
UPDATE STATISTICS options fail in Amazon RDS SQL Server
In Amazon RDS SQL Server (any version/edition), create a temp table and update statistics on it. This works fine:
IF OBJECT_ID('tempdb..#t') IS NOT NULL
DROP TABLE #t;
GO
CREATE TABLE #t (id INT NOT NULL);
GO
UPDATE STATISTICS #t
GO
But try to…
Brent Ozar
- 42,296
- 45
- 201
- 356
12
votes
1 answer
logical reads on global temp table, but not on session-level temp table
Consider the following simple MCVE:
SET STATISTICS IO, TIME OFF;
USE tempdb;
IF OBJECT_ID(N'tempdb..#t1', N'U') IS NOT NULL DROP TABLE #t1;
CREATE TABLE #t1
(
r int NOT NULL
);
IF OBJECT_ID(N'tempdb..##t1', N'U') IS NOT NULL DROP TABLE…
Hannah Vernon
- 68,431
- 22
- 166
- 303
11
votes
1 answer
Index usage on a temporary table
I have two rather simple queries. The first query
UPDATE mp_physical SET periodic_number = '' WHERE periodic_number is NULL;
and it's plan
duration: 0.125 ms plan:
Query Text: UPDATE mp_physical SET periodic_number = '' WHERE…
Artem Zefirov
- 223
- 1
- 3
- 6
10
votes
1 answer
Why using a local temp table (instead of a global temp table or a regular table) influences the Query Optimizer to choose a poor query plan?
This Question brings a situation where the Query Optimizer chooses poorly the seek predicate among the existing predicates of a simple query. After running some tests I got to the conclusion that the poor decision is due to the use of a local temp…
Ronaldo
- 4,955
- 1
- 9
- 40