What are the 2 types of Temporary Tables in SQL Server?
1. Local Temporary Tables
2. Global Temporary Tables
What is the difference between Local and Global Temporary Tables?
Local Temporary Tables:
1. Prefixed with a single pound sign (#).
2. Local temporary tables are visible to that session of SQL Server which has created it.
3. Local temporary tables are automatically dropped, when the session that created the temporary tables is closed.
Global Temporary Tables:
1. Prefixed with two pound signs (##).
2. Global temporary tables are visible to all the SQL server sessions.
3. Global temporary tables are also automatically dropped, when the session that created the temporary tables is closed.
Can you create foreign key constraints on temporary tables?
No
Do you have to manually delete temporary tables?
No, temporary tables are automatically dropped, when the session that created the temporary tables is closed. But if you maintain a persistent connection or if connection pooling is enabled, then it is better to explicitly drop the temporary tables you have created.
However, It is generally considered a good coding practice to explicitly drop every temporary table you create.
In which database, the temporary tables get created?
TEMPDB database.
How can I check for the existence of a temporary table?
 |
How to create temporary Table????
ReplyDeleteTo create a local temporary table use single # symbol
DeleteCreate Table #LocalTempTable(ID int, Name varchar(50))
Insert the data into the temporary table
Insert into #LocalTempTable values(101, 'Tom')
Select the Data
Select * from #LocalTempTable
To drop the temporary table, use drop table
Drop table #LocalTempTable
-----------------------------------------------------------
To create a global temporary table using two ## symbols
Create Table ##GlobalTempTable(ID int, Name varchar(50))
Insert the data into the temporary table
Insert into ##GlobalTempTable values(101, 'Tom')
Select the Data
Select * from ##GlobalTempTable
To drop the temporary table, use drop table
Drop table ##GlobalTempTable
Usually it is best to create a temp table through the create function.
ReplyDeleteFor instance if you were to check if that temp table existed and then drop/create a new one and insert data it would go something like this.
IF object_ID('tempdb..##temptable') IS NOT NULL
DROP TABLE ##TEMPTABLE
CREATE TABLE ##TEMPTABLE
INSERT ##TEMPTABLE
SELECT e.EMPLOYEE
from employee e
where e.employeename = frank
This would check for the table and drop it if it exists then would create a new global temp table (indicated by the ##) and insert all employees with the name frank into this temp table.
Hi Venkat,
ReplyDeleteSQL interview question
what if 1 lac transaction is happening per record 1sec, how will you do this? Also, how do you check if in between transaction is not completed due to any unusal trermination.And also to check how mny records have been successfully completed.
Transaction will rollback in that case.Any unusual termination will cause the transaction to rollback.So no records will be successfully updated.
Deletethank u venkat .... god bless u
ReplyDeletehi venkat i want to know why we use cursor what is cursor and benefit of that,pls help me.
ReplyDeleteGenerally we use to work with set of data.But cursor will process row by row.it will not process set of data each time.Due to this the performance is degraded.So I recommend not to use cursors unless you have very much use of cursors.Use cursors where you want to process the data row by row like if you want to update 10 rows but you want to commit the transaction after each row in that case you can use cursors.
DeleteHi Venkat,
ReplyDeleteHow to insert new record in between existing 2 record?
it is not possible to insert a record between two records.Sql server internally maintains data and what you have shown in results pane is maintained by server if you didn't mention order by clause in your query.
ReplyDeleteAlso the order depends on clustered and nonclustered indexes if you have on your columns.The data is stored in the data pages which will be maintained internally by sql server based on clustered and non clustered indexes.
do we have any keyword opposite to distinct i mean if i have to select same names from a table in one query then what to do is it possible.....???
ReplyDeleteHi this is Manoj Sharma,
DeleteAs i understood you want to ask the question how to select duplicate record so you can run following query :
select name,count(id) from tblSample as s group by name having count(id)>1
Please let me know it is worth for you or not.
Action performed in temporary table will affect the real table value..
ReplyDeleteSql appends random function as at the end of Local table's name. So we can create Local table with same name. But Global table's name has to be unique.
ReplyDeleteWhy is that?
Because Local table is available to the connection that created. Other users can have that name for their local table. But in case of global it is available to all users on different sessions. So it's name cannot be different.
Hey venkat I watched your YouTube video channel on sql server. Its great. Can you please upload a video regarding how to check which query in stored procedure performing poor and how to fix those query.
ReplyDeleteHey venkat, I like to add one more question regarding temporary table with this. It will be useful for others also.
ReplyDeleteHow to copy the structure of the temporary table from one table to another? i.e., copying only structure and no data?
I'll copy the data as well and then truncate or delete *.
DeleteHi ,Copy the structure of the temporary table from one table to another table like below syntax:
DeleteSelect * into #Temp1 from tblEmployee
Select Top 0 * into #Temp2 from #Temp1
Select * into #Temp1 from tblEmployee where 1=2
Deleteselect * into tempTable from tblemployee where 1 = 0
DeleteQUESTION CAN YOU CREAT VIEW ON TEMPORARY TABLES?
ReplyDeleteWe cannot create Views or Function on Temporary tables.
DeleteWhat type of constraints can we create in temporary table?
ReplyDeleteWhat type of indexea can we create on temporary tables?
ReplyDeleteTablevariable has a scope just for that particular batch in which it was created where temp tables has different scope (local and global)
ReplyDeleteHi Venkat,
ReplyDeleteFirst of all I would like thank you very much for wonderful video session(youtube) on SQL.
My Question is in which scenario we use temporary table? Any specific example in real time
Hi
ReplyDeleteVenkat,
Thanks for your all useful videos. My Question to you when query is running slow what is your step to do as a DBA?
QUESTION CAN YOU CREAT procedure ON TEMPORARY TABLES?
ReplyDeleteYes we can but not function
Delete