top button
Flag Notify
Site Registration

SQL: Where does the table variables and temporary tables exist in the memory?

+3 votes
296 views

Where does the table variables and temporary tables exist in the memory, in memory or physical drive?

posted Jan 4, 2014 by Atul Mishra

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

Temp Table and Table Variable both are created in TempDB and not in memory. So the answer to your problem is these are created in the physical drive?

For more detail please refer the following blog which has a proof by example:
http://blog.sqlauthority.com/2009/12/15/sql-server-difference-temptable-and-table-variable-temptable-in-memory-a-myth/

answer Jan 5, 2014 by Luv Kumar
Similar Questions
+4 votes

how to check if table exist and if it doesnt exist create table in sql server 2008

0 votes

I hope you guys could help me with solving the following problem.
I am new to SQL (using SQL Server Management Studio 2017).

My goals is to combine the keys of different tables into one table.
Which is shown below. For this example i have 4 source tables of which i only display the keys;

ContractTable:

+------------+---------+
| ContractId | PartyId |
+------------+---------+
| C01        | 1       |
+------------+---------+
| C02        | 2       |
+------------+---------+
| C03        | 3       |
+------------+---------+
| C04        | 4       |
+------------+---------+
| C05        | NULL    |
+------------+---------+
<!-- end snippet -->

PartyTable:

+---------+
| PartyId |
+---------+
| P1      |
+---------+
| P2      |
+---------+
| P3      |
+---------+
| P4      |
+---------+
| P5      |
+---------+
| P6      |
+---------+
| P7      |
+---------+
| P8      |
+---------+

RentalObjectTable:

+----------------+------------+----------+
| RentalObjectId | ContractId | ObjectId |
+----------------+------------+----------+
| R1             | C5         | O1       |
+----------------+------------+----------+
| R2             | NULL       | O2       |
+----------------+------------+----------+
| R3             | C4         | O3       |
+----------------+------------+----------+
| R4             | C1         | O4       |
+----------------+------------+----------+

ObjectTable:

+----------+
| ObjectId |
+----------+
| O1       |
+----------+
| O2       |
+----------+
| O3       |
+----------+
| O4       |
+----------+
| O5       |
+----------+
| O6       |
+----------+

Together they should form the Key/FactTable

+---------+------------+----------------+----------+
| PartyId | ContractId | RentalObjectId | ObjectId |
+---------+------------+----------------+----------+
| P1      | C01        | R4             | O4       |
+---------+------------+----------------+----------+
| P2      | C02        | NULL           | NULL     |
+---------+------------+----------------+----------+
| P3      | C03        | NULL           | NULL     |
+---------+------------+----------------+----------+
| P4      | C04        | R3             | O3       |
+---------+------------+----------------+----------+
| P5      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| P6      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| P7      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| P8      | NULL       | NULL           | NULL     |
+---------+------------+----------------+----------+
| NULL    | C5         | R1             | O1       |
+---------+------------+----------------+----------+
| NULL    | NULL       | R2             | O2       |
+---------+------------+----------------+----------+
| NULL    | NULL       | NULL           | O5       |
+---------+------------+----------------+----------+
| NULL    | NULL       | NULL           | O6       |
+---------+------------+----------------+----------+

Uptill now i tried the following:

CREATE TABLE FACTS(
ObjectId NVARCHAR(255),
RentalObjectId NVARCHAR(255),
ContractId NVARCHAR(255),
PartyId NVARCHAR(255)
);

INSERT INTO FACTS(ContractId)
SELECT ContractId
FROM ContractTable

UDATE FACTS
SET FACTS.Party_id = PartyTable.PartyId
FROM FACTS FULL OUTER JOIN
PartyTable on PartyTable.PartyId = FACTS.PartyId;

Using that same update statement for the other field.
However this does not work.
I do not get the values on which the query does not find a match.

...