Questions tagged [temporary-tables]

The use of temporary table objects (not table variables) including semantics, design, performance optimization, and indexing. See also [table-variable].

Filter by
Sorted by
Tagged with
2
votes
1answer
66 views

Sql Server string concat with order by and subquery

While working on some dynamic sql production code we encountered code that's concatenating a bunch of strings and a subquery and then more string concatenation. The goal of the code is to populate a ...
0
votes
2answers
66 views

Are key lookups from non-clustered indexes always slower than a second query that does the lookup?

I've noticed in my system, whenever a non-clustered index is used in a query that has to also do a key lookup to get the additional fields being selected, it's faster for me to instead do two queries. ...
32
votes
2answers
3k views

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 ...
0
votes
0answers
10 views

Does the Query Optimizer have the right to create Temporary (Materialized) Views if it enhances performance?

Does the Query Optimizer have the right to create Temporary (Materialized) Views which can enhance performance? Sorry if this question seem very trivial. Or in other words, does the query optimizer ...
2
votes
1answer
106 views

Is there an easy way to SELECT FROM another session's temp table?

Is there an easy way to select the data from another session's temp table? I need to do so ad-hocly to debug an issue that I have limited visibility on otherwise. E.g. if I have access to TEMPDB, is ...
3
votes
1answer
156 views

If sp_ExecuteSql creates a new session, how come I can access a local temp table created (prior to it's execution) outside of the dynamic SQL?

If local temp tables are only available to the current session, and sp_ExecuteSql creates a new session to execute the dynamic SQL string passed into it, how can that dynamic SQL query access a temp ...
0
votes
2answers
200 views

msdb.dbo.sp_send_dbmail temp table does not exist

I have created a temp table and would like to email the results I am getting the following error. Query execution failed: Msg 208, Level 16, State 1, Server PRODYCHDBX3\X3V7, Line 1 Invalid ...
0
votes
1answer
30 views

Unable to Insert Casted XML Data into New Column in Temporary Table

Question How can I insert CAST(event_data as XML) data into a newly added column in a temporary table with the following statement: INSERT INTO #Unique_Logins (event_data_xml) SELECT CAST(...
1
vote
1answer
66 views

Using WITH together with CREATE TEMPORARY TABLE AS SELECT … in Postgres?

I would like to create a temporary table using a select statement that uses temporary views specified in a WITH statement (not the WITH statement of CREATE TEMPORARY TABLE). e.g. something along the ...
0
votes
0answers
15 views

How to create a linked table to an SQL global temporary table

In my Access database VBA code, I would like to create a linked table to a global temporary table in SQL Server. I can't seem to get it to work. This is what I have tried: Dim adors as new ADODB....
1
vote
2answers
54 views

Replace function removing Ones / 1 when removing char(185) (superscript 1)

Ok as the title suggests this is occuring. When using the REPLACE function on char(185) for data in a temporary table, ones ("1") are being removed as well. SQL SERVER 2008R2 UNICODE Data DB ...
0
votes
2answers
43 views

How to split a table in SQL Server into two different related tables inserting foreign key?

I have a table OriginTable with the following structure EventID | Date | Value1 | Value2 | Value3 ----------------------------------------------------------- 5 | 2019-10-07 | ...
1
vote
2answers
188 views

SQL Server - global temporary table vs normal table

I have a stored procedure which I use to load csv files to SQL Server. This will initially bulk insert the file into a global temporary table then at the end it will do: select cast(column1 as int) [...
0
votes
1answer
184 views

How to solve a mail issue Failed to initialize sqlcmd library with error number -2147467259?

I got an error while trying to send an email: Msg 22050, Level 16, State 1, Line 0 Failed to initialize sqlcmd library with error number -2147467259. Here is my query: DECLARE @Table TABLE ( ...
1
vote
1answer
65 views

Postgres long running query not blocking table operations

We have a 3rd party service that imports data into a local postgres instance, that we then query/copy into our own view-data system. These queries can yield millions of rows, so the import of them to ...
0
votes
1answer
86 views

Aurora postgres grant permission to pg_attribute

In the process of migration from local postgres version to and RDS aurora postgres version. I'm using temporal tables and do for that reason have temporal tables that are inhirited from the origin. ...
0
votes
1answer
117 views

insert results from store procedure with parameters into table

I have a stored procedure that I want to insert into a table, the stored procedure however has parameters I have to pass, how can I do this? DECLARE @RC int DECLARE @startweek varchar(20) DECLARE @...
1
vote
0answers
53 views

How to avoid running multiple selects with the same subquery join?

I am using MySQL 5.7.26. I have 12 SELECT queries which run the same subquery join; since the subquery in a join is very complex (can be more complex, it is a filter query in a web app) I want to ...
0
votes
1answer
42 views

Oracle Global Temp table - data is truncated but the table remains in the DB ! is this an expected behavior or a configuration?

We are trying to create global temp table using Create global Temporary table t1 ( id NUMBER, description VARCHAR2(20) ) ON COMMIT Delete Rows; and trying to drop it at the end manually but ...
0
votes
1answer
28 views

Query to filter a table based on a comparison applied to the same table?

Case & Problem: Consider a corporate building with some rooms and departments that need authorization to enter. Employees have to scan their badges to be authorized. If the employee is inside (...
0
votes
0answers
36 views

Allocating Hours From Operations Table to Available Hours in Availability Table

I have a scheduling problem… The database is SQL Server 2017. The data comes from one Table and one View… Operations_TBL Operation_ID Resource_ID_Link Operation_Start_By_Date ...
2
votes
2answers
529 views

MySQL - Can we use the same temporary table name in different stored procedure?

In our database, we generate statistics by periodically calling a large number of stored procedures in the event. Considering there are two SP auto_update_daily_users auto_update_daily_orders Is ...
0
votes
0answers
68 views

Allocating Rows From One Table to Available Date Range in Second Table

I designed a Shop Floor Scheduling System and it’s been running well for years, but the managers are now asking for something more precise and what they are asking for puts me outside my comfort zone. ...
0
votes
1answer
52 views

Use Common Table Expression (CTE) to reduce locks (max_locks_per_transaction)

I am getting "ERROR: out of shared memory" presumably because my plpgsql function drops/creates a lot of temporary tables. Searching for a solution to the issue, I found this answer that says: ...
2
votes
1answer
169 views

Why can't temporary tables reference regular (non-temp) tables in PostgreSQL?

Why can't temporary tables references non-temp tables in PostgreSQL? When I run the following CREATE TABLE foo ( a int PRIMARY KEY ); CREATE TEMP TABLE bar ( a int REFERENCES foo ); I get, ERROR:...
2
votes
1answer
200 views

What types of locks are held on global temporary tables?

I am wondering if: the isolation level is Read Committed data is inserted or deleted or read are the same locks (shared for reading and exclusive for insert/delete) used for global temporary table ...
7
votes
1answer
197 views

Using `min_active_rowversion` for global temporary tables

I use global temporary tables for easy integration testing of my SQL Server backed application. But the function min_active_rowversion does not seem to include global temporary tables, like seen ...
0
votes
1answer
665 views

How to create and fill multiple session temp tables with dynamic SQL?

I have a column that holds JSON data. The table can be filtered for sets of similar JSON data that will have mostly identical JSON keys in the JSON column. I can dynamically collect the set of JSON ...
11
votes
1answer
445 views

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'...
2
votes
1answer
265 views

Safe to use the same temporary table name in parallel transactons?

Using postgreSQL 10 or 11, would it be safe creating temporary tables with the same name, each carrying different data? I do not intend to share the data between any sessions/connection/transactions. ...
-1
votes
1answer
222 views

executing SQL query with temp tables using 1.6jdk

I created a batch to run a query that has temp tables as the below example IF OBJECT_ID ('tempdb..#TEMP1') IS NOT NULL DROP TABLE #TEMP1 select * into #TEMP1 from cutomer IF OBJECT_ID ('tempdb..#...
2
votes
1answer
2k views

How to clear temp database content in SQL Server?

As the title implies, I am going to clear the content of my temp database in SQL Server. SolarWinds is generating a critical alert and I want to clear the tempdb workfiles and worktables. How can I ...
5
votes
3answers
1k views

How to inspect global temp table from outside the transaction it is created in

To help debugging a batch of SQL (that is run inside a transaction), inside the transaction I dump some data into a global temporary table. The global temporary table is created inside the transaction ...
0
votes
0answers
423 views

PostgreSQL Optimize bulk values CTE and SELECT INTO TEMP TABLE to prevent OOM kills

Recently we moved some code from Rails way to raw SQL for performance reasons. We have some jobs which fetch some data from APIs, data can be in 100K+ rows of count sometimes. This data needs to be ...
0
votes
2answers
64 views

MariaDB Spider Engine UDF spider_bg_direct_sql can't find TEMPORARY table

I am testing the Spider engine in MariaDB. In version mariadb-10.1.12-linux-x86_64 the UDF works, but there are some issues so I want to upgrade to a newer version. However, mariadb-10.2.18-linux-...
7
votes
4answers
717 views

Does LocalDB support temporary tables?

Are SQL Server temporary tables (prefixed with #) supported in LocalDB instances?
0
votes
2answers
163 views

INNER JOIN is extremly slow on some rows and create temp table

The MySQL query SELECT `p`.*, `p.Author`.* FROM `post` AS `p` INNER JOIN `user` AS `p.Author` ON `p`.`userid` = `p.Author`.`userid` WHERE ( ( `p`.`threadid` = 29780 ) AND ( `...
0
votes
1answer
62 views

Temp tables not being created in SQL Server Azure [duplicate]

I have created a stored procedure that creates multiple temp tables for use in the sproc and then drops them at the end of the sproc. The problem I am encountering is I can execute the stored ...
1
vote
1answer
2k views

“The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.” after adding indexes to temp tables

Recently, I added some indexes on temp tables to procedures which prepare data for data warehouse. On a testing db, tests of these procedures worked fine but when running on client's server it failed ...
1
vote
1answer
3k views

Performance difference between Text and Varchar in Mysql

I was experimenting with text and varchars in mysql 5.7. I create a simple mysql table as below CREATE TABLE small_table ( pk int(11) NOT NULL AUTO_INCREMENT, varc varchar(255) DEFAULT NULL, txt ...
1
vote
1answer
81 views

Finding temporary table name in MariaDB

As I reported some days ago to MariaDB bug tracker the following bug: https://jira.mariadb.org/browse/MDEV-17420, slave replicas with version 10.2.x are leaking temporary tables on MyISAM files. Our ...
1
vote
2answers
34 views

mysql - calculate traffic for tmp tables

I'm thinking of placing tmpdir to SSD - to increase speed of working with sorting data in tmp tables on disk. Our base creates a lot temporary tables on disk during sorting, merging, joining an so on. ...
0
votes
1answer
15 views

Dropping rows based on the previous selection statmnet

I have a database with following tables: temp_1, temp_2.... and cached_tbl. Inside cached_tbl I have column table_name and in corresponding rows I store all the temp table names I mentioned above. SO ...
0
votes
1answer
422 views

Truncating TEMP table before insert leads to big amount of logical reads on TEMP TABLE

Setup: We have a complex set of queries, that runs quite often (about 3000 times per hour) for different users. At the start of the process, .NET application creates a set of temp tables during the ...
1
vote
1answer
445 views

Why do LocalTemp table showing with lengthy underscore under Temporary Tables of tempdb in SSMS 2017 Express Edition? [duplicate]

In my Microsoft SQL Server 2017 Express Edition, I have created a local temp table using below create statement, create table #localtemptable (tempid int,tempname varchar(20) ) After creating #table,...
3
votes
1answer
176 views

Reasons for not Globally enabling Trace Flag 2453 - Cardinality on Table Variables

I've been doing some reading on the improvements that Trace Flag 2453 can give on the performance of Table Variables by maintaining statistics and was wondering what would be the reasons you would ...
4
votes
3answers
226 views

Temp Table Filtered Index Creation is Blocked When Stored Procedure is Run Multiple Times Concurrently

Are there any known blocking issues in SQL Server 2014 that are specific to creating one or more filtered indexes on a temp table after the initial table creation? (i.e. not an inline index creation) ...
1
vote
0answers
65 views

Why does the Select statement stalls when executed directly in sequence?

When executing the following in workbench or in code, the Drop table/Create Table works ok. The Select statement stalls when executed in one go. If the table is MyIsam, it works ok. If executed [...
6
votes
1answer
4k views

MS SQL Server: Do multiple queries in a batch ever execute in parallel and if so what happens when the second query is dependent on the first?

If I have a batch of two different SELECT statements, is it possible for them to be executed in parallel (if the SQL Optimizer deems it to be the most efficient execution method)? If the first SELECT ...
0
votes
1answer
35 views

How to get the consolidated ServiceID with the sum of File Size associated with over an year

Can anyone please help me on below to get the consolidated ServiceID with the sum of File Size associated with it over an year. Complete Code (For reference) If(OBJECT_ID('tempdb..#temp1') Is Not ...

1 2 3 4 5