temp-tables

Create a temporary table in a SELECT statement without a separate CREATE TABLE

by

CREATE TEMPORARY TABLE IF NOT EXISTS table2 AS (SELECT * FROM table1) From the manual found at http://dev.mysql.com/doc/refman/5.7/en/create-table.html You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current session, and is dropped automatically when the session is closed. This means that two different sessions can use … Read more

Which are more performant, CTE or temporary tables?

by

It depends. First of all What is a Common Table Expression? A (non recursive) CTE is treated very similarly to other constructs that can also be used as inline table expressions in SQL Server. Derived tables, Views, and inline table valued functions. Note that whilst BOL says that a CTE “can be thought of as … Read more

When should I use a table variable vs temporary table in sql server?

by

Your question shows you have succumbed to some of the common misconceptions surrounding table variables and temporary tables. I have written quite an extensive answer on the DBA site looking at the differences between the two object types. This also addresses your question about disk vs memory (I didn’t see any significant difference in behaviour … Read more

Local and global temporary tables in SQL Server

by

Table variables (DECLARE @t TABLE) are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends. Local temporary tables (CREATE TABLE #t) are visible only to the connection that creates it, and are deleted when the connection is closed. Global temporary tables (CREATE TABLE ##t) are visible … Read more

SQL Server: Is it possible to insert into two tables at the same time?

by

In one statement: No. In one transaction: Yes BEGIN TRANSACTION DECLARE @DataID int; INSERT INTO DataTable (Column1 …) VALUES (….); SELECT @DataID = scope_identity(); INSERT INTO LinkTable VALUES (@ObjectID, @DataID); COMMIT The good news is that the above code is also guaranteed to be atomic, and can be sent to the server from a client … Read more

What’s the difference between a temp table and table variable in SQL Server?

by

There are a few differences between Temporary Tables (#tmp) and Table Variables (@tmp), although using tempdb isn’t one of them, as spelt out in the MSDN link below. As a rule of thumb, for small to medium volumes of data and simple usage scenarios you should use table variables. (This is an overly broad guideline … Read more