Different types of temp tables
Step 1:
We have different types of temporary data storage objects, probably we call as temp tables.
1. TempTable
2. TableVariable
3.CTE (Common Table Expression)
.
1. TempTable
Again we have #table(Local Temp Table) and ##table(Global Temp Table)
A local temp table, we will use in one session. It means that anywhere in the same SPID.
In above images, I have created one # temp table. I inserted the data into that and executed temp table separately. It gives the result. Because same(55) session.
The same # temp table, I am trying to access from SPID 57. It gives an error. It means that SCOPE (Validity) of # table is limited to that particular session only.
Now, it is ##(Global Temp Table).
I have created in SPID(55). I tried to access. It is giving fine results.
Look into below image.
I can able to access it. Because the SPID(55) connection is opened.
Now, I closed SPID 55 and trying to access ##table from SPID 57. It is giving error.
It means, if the connection is in open state (i.e., where we created the ## Table), we can access this table from anywhere. Once it closed, ## table also expires.
2. Table Variable.
Why we call this a Table Variable?.
Declare @Table Table(BusinessEntityID int,
PhoneNumber VARCHAR(50),
PhoneNumberTypeID int,
ModifiedDate datetime)
We usually declares the variables with '@'. (EX: Declare @String VARCHAR(100) )
This table name also given like @Table. That's why we call it as Table Variable.
I have created Table Variable, and Inserted the data into it.
INSERT INTO @TblVariable
SELECT TOP 10 * FROM Person.PersonPhone
SELECT * FROM @TblVariable
I selected all the script and executed once.
It is giving result fine.
Now, see below image.
I am executing SELECT * FROM @TblVariable separately.
But it throws an error.
It means, the table variable must be in a batch. If we execute individually, it wont give any result.
# and ## tables will stored in tempdb system database. But table variable will store in RAM( Memory).
If more data in table variable, it consumes memory, so that server will get slow.
Same will be applicable for CTE which are meant for table variable.
