Questions tagged [temporary-tables]
The use of temporary table objects (not table variables) including semantics, design, performance optimization, and indexing. See also [table-variable].
298 questions
-1
votes
1
answer
49
views
OBJECT_ID fails to find existing temp table [closed]
I'm using temp tables to stage data before inserting into production. As a couple of things use them, before anything else I do a:
IF OBJECT_ID(N'##Projects_TEMP') IS NOT NULL
TRUNCATE TABLE ##...
- 222
11
votes
1
answer
486
views
What could cause a SQL Server non-cached temporary table to NOT trigger a recompile when a large amount of rows have changed?
I have observed (and reproduced) the following scenario with SQL Server 2022.
The pattern in use
code is executed via sp_executesql (no stored procedure is involved)
The first query selects data into ...
- 898
0
votes
0
answers
32
views
Temporary relation files not removed after sessions ends - Single PostgreSQL instance PostgreSQL 13
We are facing default temporary tablespace disk space full issue due to lots of below temporary relations files created but not removed after ending session
1.1G t756_1536213694.409
1.1G ...
- 143
2
votes
1
answer
156
views
What kinds of operations could cause a "Temp table changed" recompilation?
Frequent recompilation happens in one SP with 'Temp table changed' cause. What kinds of operations on temp table would trigger this? There are no alter and create index operations on temp table, just ...
- 43
6
votes
1
answer
477
views
How does sp_executesql interact with temporary tables?
I'm at a loss as to why some SQL leveraging sp_executesql is working the way it is when temporary tables are involved, along with INSERT and SELECT - INTO. I'm using SQL Server.
Here is the code in ...
- 171
0
votes
0
answers
71
views
How do I feed the output arrow (`Input0_ProcessInputRow()`) of a second Script Component with data from a temp table of the first Script Component?
Here is the full setup and code that takes up this answer at DFT drops connection and its temporary table after leaving any DFT item, tested with two Script Components. How do I keep the temp table ...
- 336
0
votes
1
answer
161
views
DFT drops connection and its temporary table after leaving any DFT item, tested with two Script Components. How do I keep the temp table alive?
Links
This takes up:
Using temporary tables in SSIS flow fails - Stack Overflow
Is it possible to use a temp table in a data flow source?; answer is:
'No', since it cannot be passed without being ...
- 336
0
votes
2
answers
764
views
How do I create and fill a temporary table with incoming data from a Data Source item without leaving the SSIS Data Flow Task C# Script Component?
I came to ask this question after coding in a script component for some time, testing mainly how to loop over columns of the Row object, see Looping Through Columns in SSIS Script Component - Stack ...
- 336
0
votes
0
answers
61
views
Do Primary Keys On Table Variables Have No Statistics At All? [duplicate]
My confusion is down to an apparent contradiction in principles. Primary keys are indexes and those always have statistics, but table variables notoriously always don't have statistics. What follows ...
- 855
0
votes
0
answers
140
views
How to insert data into temporary table from the table having encrypted columns in sql server
I am trying to insert data into a temporary table(#table1) from the table(table2) having encrypted column in it.
I am getting below error while trying to insert data into temporary table from the ...
- 11
0
votes
1
answer
390
views
Why use a memory-optimized table variable instead of a temp table or normal table variable?
Table variables have a bad reputation, mostly due to cardinality estimates. However, this reputation was earned before the introduction of memory-optimized table variables. Microsoft covers some use-...
- 855
0
votes
1
answer
199
views
Switching table partition into temporary table
I'm trying to switch a table partition into a temporary table with the following query:
create partition function [PFCT_T](datetime2) as range right for values ()
create partition scheme [PSCH_T] as ...
- 27
-1
votes
1
answer
88
views
Is it possible to create fast (ram only) temporary table for inter software communication purposes in MS SQL Server?
I was wondering that is it possible to create a table that is really fast and temporary (basically one that resides in ram) in MS SQL Server 2005 or later for inter software communication purposes? Do ...
2
votes
1
answer
1k
views
Is there any benefit to WITH RECOMPILE or OPTION (RECOMPILE) in a stored procedure that uses temp tables at every stage?
Suppose that I have a stored procedure. It does not call any other stored procedures and is not called by any others, nor is it called concurrently. Every step in this stored procedure either creates ...
- 855
1
vote
3
answers
690
views
MySQL Temporary Tables and Connection Pools
This is more of a "please confirm/correct my understanding" question.
Background
I connect to MySQL using JDBC, the connection pool has around 250 connections, most of which seem ...
- 127
0
votes
1
answer
159
views
Why does inserting a value into a temp table's BINARY(8) column cause an error when casting the same value to BINARY(8) does not?
When I execute this:
DROP TABLE IF EXISTS #QueryHashes;
CREATE TABLE #QueryHashes (query_hash BINARY(8) NOT NULL PRIMARY KEY);
PRINT CAST(0x0169857CCD5BBBE4E AS BINARY(8)); -- just testing that the ...
- 2,191
1
vote
1
answer
595
views
Will Postgres reclaim space when a temporary table is dropped?
I'm using a large number of TEMP TABLES and want to make sure I'm not leaking storage.
I create and use the table in a transaction with CREATE TEMP TABLE mytable ... ON COMMIT DROP.
I'm having trouble ...
- 1,655
1
vote
1
answer
1k
views
what queries in mariadb create .mad and .mai temporary files
During our nightly backup I saw that two files vanished while the backup was being performed:
file has vanished: "/tmp/#sql-temptable-3ab-aca6d-316f6.MAD"
file has vanished: "/tmp/#sql-...
- 13
0
votes
1
answer
74
views
Compose a complex query in Postgresql
I have a huge table like following:
CREATE TABLE public.huge_table (
sampl_day date NOT NULL,
tick_time timestamp(6) with time zone NOT NULL,
crit_feat integer NOT NULL,
---
--- ...
- 359
0
votes
0
answers
199
views
Temporary rows in static table in SQL Server
I'm looking for a way to insert temporary rows in a static table. These rows are temporary because are used only in processing.
The reasons are:
I wish not to work in transaction because it's a huge ...
- 127
2
votes
0
answers
160
views
MySQL release memory allocated for a temp table
I'm using 5.6 MySQL database to insert tens of millions records between multiple tables.
Before the insertion I send all my data into a temp memory table, for that reason I have increased both ...
- 121
0
votes
0
answers
209
views
Is a dbo.SORT table in a user database confined to only 1 data file?
A user thought he was exhausting tempdb because of this error:
'Could not allocate space for object 'dbo.SORT temporary run storage: 422291993526272' in database '<USER_DB>' because the '...
WillemG
-2
votes
1
answer
15k
views
What is the correct way to check and delete temp table?
Approach 1:
IF OBJECT_ID('tempdb..#MyTempTbl') IS NOT NULL
DROP TABLE #MyTempTbl;
Approach 2:
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
WHERE [name] = N'#MyTempTbl')
DROP TABLE [#...
- 3,489
1
vote
0
answers
531
views
Azure SQL Error "Invalid object name '##{myTempTableName}'
I am getting an error in my stored procedure: "Invalid object name '##{myTempTableName}' for 3 global temporary tables. There are several temp tables used in the procedure, but I am getting this ...
- 11
0
votes
1
answer
193
views
How does a serializable transaction level affect global temporary tables in SQL Server? Creating table affects it?
I have a knowledge gap in SQL Server 2019 and if someone could explain it to me how the following works appreciate it. This will be a long post as I want to explain the full context as best I can ...
0
votes
1
answer
280
views
Solutions for fixing stored procedures that rely heavily on global temporary tables?
I am working on re-writing some stored procedures. The last issue was an abuse of EXECUTE AS USER='someUser' to use the default schema and work against those.
The second thing I just noticed though ...
1
vote
1
answer
2k
views
How does PostgreSQL resolve pg_temp?
pg_temp is an alias for current session's temporary-table schema.
Likewise, the current session's temporary-table schema, pg_temp_nnn, is always searched if it exists. It can be explicitly listed in ...
- 111
3
votes
1
answer
581
views
Invalid Object Name after Creating a Table using a while loop
I created this loop to create and populate a temporary table for each int representing a day. The tables are created and properly executed but i get Invalid Object Name everytime i try to print a ...
- 31
2
votes
2
answers
1k
views
CTE vs. temp table for batch deletes
Two-part question here.
We have a large table (between 1-2 million rows) with very frequent DML operations on it. During low volume periods, we have an agent job to remove older rows to keep the ...
- 255
3
votes
1
answer
222
views
Valid for WITH clause to use existing table name?
Suppose I have a table Entry. Is it valid for a WITH clause to replace the Entry table for the duration of a query? I tried with SQLite and MySql and it seems to work, but I want to be sure this is ...
- 133
-1
votes
1
answer
180
views
When DML and DDL changes occur on a Temporal Table do the correlating changes in the History table get logged in the Transaction Log as well?
When DML and DDL queries are executed against a Temporal Table, those changes are appropriately propagated to the correlating History table. E.g. an UPDATE to the Temporal Table results in a new ...
- 39.5k
0
votes
1
answer
562
views
Teradata: Temporary Table vs Create and Drop Table
Currently I have seen an ETL process where a user created tables that are used to store an intermediate result and then drop the tables at the end of the ETL process.
At first glance it seemed to me ...
3
votes
2
answers
2k
views
UPDATE FROM with a large table is slow and uses Seq Scans
I have a large table (ultimately maybe a billion rows but currently ~26 million) for which I want to set a flag on the highest PK for a given grouping, in a one-off batch.
I chose to create a ...
- 1,655
0
votes
1
answer
63
views
When Insert Press Name Tech Id Take Wrong Number Where Press Name Exist?
I work on SQL server 2017 I have table #Pressfeature as below
create table #Pressfeature
(
PartId int,
PressName varchar(300),
TechId int
)
insert into #Pressfeature(PartId,PressName,...
2
votes
1
answer
212
views
Alter the collation of a column in temp-file
I have a routine in a DB that uses a temp-file. But the collation of the DB doesn't match the collation of the temp-DB. I can't change the collation of the DB, it's a client DB.
I tried this:
BEGIN
...
- 31
3
votes
2
answers
740
views
How can Temp Table creation time be reduced in terms of CPU time?
Inside a frequently executed stored procedure (sometimes up to 10-15 times per second), there are two particular statements that show up in Top 10 heaviest queries by cumulative CPU impact
These are ...
- 5,964
0
votes
1
answer
3k
views
How to create multiple temp tables using records from a CTE that I need to call multiple times in Postgres plpgsql Procedure?
I am already using a CTE expression within a plpgsql Procedure to grab some Foreign Keys from (1) specific table, we can call it master_table. I created a brand new table, we can call this table ...
- 121
1
vote
1
answer
3k
views
Automatically materialize CTE? (instead of temporary table CREATE and INSERT)
I’m a novice trying to learn about query optimization and temporary tables in Oracle.
The examples I’ve seen for Oracle temporary tables involve CREATE TABLE and INSERT INTO statements.
CREATE ...
- 1,497
3
votes
3
answers
2k
views
Reducing Temp Table Scans when joining two temp tables using OR conditions
I'm working on a complicated query. I have up to this point been able to refactor to reduce execution time as well as number of scans and reads. At this point in the query, we have two temp tables ...
- 115
9
votes
2
answers
853
views
How does the optimizer treat yet-unpopulated #temp tables when estimating cardinality?
I've been building stored procedures and can't find any documentation on how the SQL Server optimizer handles #temp tables.
If I have 4 joins to a #temp table where that #temp table is created DURING ...
2
votes
1
answer
6k
views
There is already an object named PK_MyPrimaryKey in the database ( primary index on temp table)
Simplified code
var sql = @" create table #Temp(
int Id NOT NULL
CONSTRAINT [PK_TempId] PRIMARY KEY (Id)
)
insert into #temp(id) select (...
- 195
0
votes
2
answers
2k
views
ORA-01652 - unable to extend TEMP segment?
Having an issue that I can't seem to find any solutions to online.
[72000][1652] ORA-01652: unable to extend temp segment by 256 in tablespace TEMP Position: 581
One of our users runs a query ...
- 11
0
votes
1
answer
74
views
Sql Server consuming lot of memory Creating Bottleneck for my web application Hosted in the same server
HI I have a server with 64 GB Ram and I have assigned around 52GB to the SQl Server itself. However Even this has effected the performance of the server and the web application hosted in the same ...
1
vote
1
answer
1k
views
MySQL indexing on 11 million rows
I have a development and production server, both with different amounts of RAM. Development machine has 8G, while production has 64G.
I'm importing a large table from development to production, but ...
- 111
0
votes
1
answer
3k
views
Create an Azure Synapse temp table from a SELECT with ORDER BY
I have the following statement that works when I omit the ORDER BY clause, or if I run it as a select and omit the create table part, but I need both to ensure my generated key is sequenced correctly
...
- 69
3
votes
5
answers
11k
views
Temporary table is full
The table '/mnt/disks/data/tmp/#sql58de_a0f6_7' is full
The query is quite complex/big. Lots of joins in joins and unions, sorting, grouping, ...
1 out of 10 the result is returned. Otherwise it ...
- 143
2
votes
3
answers
5k
views
Should I use a temp table or join
I am creating a stored procedure in sql server 2019 that needs to use multiple select statements to get a parent row and then its related data. I have the primary key clustered value for the parent so ...
- 140
1
vote
1
answer
27
views
Does MySQL support defining a table inside a session without appending it to the database?
As per stated in the title. Does MySQL support defining locally a table? (as a 'local variable' perhaps?)
I understand (according to 'Source') that MySQL has 'two types of variables': (1) local and (2)...
- 113
10
votes
1
answer
525
views
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 ...
- 5,926
1
vote
0
answers
2k
views
MariaDB 10.3.27 - 100% Temporary tables created on disk despite/because of InnoDB?
I am using Mariadb 10.3 with InnoDB with a fairly high load. For some reason, I have 100% of my temporary tables created on disk, despite using Innodb and a large tmp_table_size (2G) (see Mysqltuner....
- 11