SQL Server Interview Questions on Temporary Tables



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?

29 comments:

  1. How to create temporary Table????

    ReplyDelete
    Replies
    1. To create a local temporary table use single # symbol
      Create 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

      Delete
  2. Usually it is best to create a temp table through the create function.

    For 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.

    ReplyDelete
  3. Hi Venkat,
    SQL 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.

    ReplyDelete
    Replies
    1. Transaction will rollback in that case.Any unusual termination will cause the transaction to rollback.So no records will be successfully updated.

      Delete
  4. thank u venkat .... god bless u

    ReplyDelete
  5. hi venkat i want to know why we use cursor what is cursor and benefit of that,pls help me.

    ReplyDelete
    Replies
    1. Generally 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.

      Delete
  6. Hi Venkat,
    How to insert new record in between existing 2 record?

    ReplyDelete
  7. 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.
    Also 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.

    ReplyDelete
  8. 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.....???

    ReplyDelete
    Replies
    1. Hi this is Manoj Sharma,
      As 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.

      Delete
  9. Action performed in temporary table will affect the real table value..

    ReplyDelete
  10. Sql 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.

    Why 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.

    ReplyDelete
  11. 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.

    ReplyDelete
  12. Hey venkat, I like to add one more question regarding temporary table with this. It will be useful for others also.
    How to copy the structure of the temporary table from one table to another? i.e., copying only structure and no data?

    ReplyDelete
    Replies
    1. I'll copy the data as well and then truncate or delete *.

      Delete
    2. Sailaja BhamidipyatMarch 25, 2015 at 1:42 PM

      Hi ,Copy the structure of the temporary table from one table to another table like below syntax:
      Select * into #Temp1 from tblEmployee
      Select Top 0 * into #Temp2 from #Temp1

      Delete
    3. Select * into #Temp1 from tblEmployee where 1=2

      Delete
    4. select * into tempTable from tblemployee where 1 = 0

      Delete
  13. QUESTION CAN YOU CREAT VIEW ON TEMPORARY TABLES?

    ReplyDelete
    Replies
    1. We cannot create Views or Function on Temporary tables.

      Delete
  14. What type of constraints can we create in temporary table?

    ReplyDelete
  15. What type of indexea can we create on temporary tables?

    ReplyDelete
  16. Tablevariable has a scope just for that particular batch in which it was created where temp tables has different scope (local and global)

    ReplyDelete
  17. Hi Venkat,
    First 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

    ReplyDelete
  18. Hi
    Venkat,
    Thanks for your all useful videos. My Question to you when query is running slow what is your step to do as a DBA?

    ReplyDelete
  19. QUESTION CAN YOU CREAT procedure ON TEMPORARY TABLES?

    ReplyDelete

If you are aware of any other sql server questions asked in an interview, please post them below. If you find anything missing or wrong, please feel free to correct by submitting the form below.

 
Disclaimer - Terms of use - Contact Us