none
why should we write use tempdb for #tables?

    Question

  • i just noticed that , no matter which database is active in the query window, when u create a #table, it is created inside tempdb by default.
    Then y should we write use tempdb statement before creating temp tables?

    Wednesday, February 03, 2010 6:19 AM

Answers

  • Hi

    Hi,
    We can create local & global temporary table. Temporay Tables are creating in Tempdb
    Local temporary table only visible current session.
    Global temporary table visible accross all session.
    Local Temporary table:
    Syntax:

    CREATE TABLE #TEMP
    (
    COL1 INT,
    COL2 VARCHAR(30),
    COL3 DATETIME DEFAULT GETDATE()
    )
    GO


    Here '#' means local temporary table.

    Limitation:
    1.One cannot create another temporary table with the same name in the same session.

    Global temporary table:
    CREATE TABLE ##TEMP_GLOBAL
    (
    COL1 INT,
    COL2 VARCHAR(30),
    COL3 DATETIME DEFAULT GETDATE()
    )
    GO

    Here '##' means global temporary table


    if u need to create temp table in local db use this below query

    WITH My_temp (nam, id) AS
    (
    SELECT name,id FROM test
    )
    SELECT * FROM My_temp


    Manigandan-DBA , Mark as Answer if it helps!
    sqlserver-manigandan.blogspot.com
    • Marked as answer by KJian_ Wednesday, February 10, 2010 7:53 AM
    Wednesday, February 03, 2010 6:28 AM
  • There is no need write "use tempdb" when creating temp tables because they will be **ALWAYS** created in tempdb; there is no other location they can land in

    For stored procedures , Local Temp tables will be dropped when the execution finishes ; that's why you don't need a "drop table #table" at end of SP

    HTH

    Please mark as answer if you think this answers your questions
    • Marked as answer by KJian_ Wednesday, February 10, 2010 7:52 AM
    Wednesday, February 03, 2010 9:25 AM
  • i just noticed that , no matter which database is active in the query window, when u create a #table, it is created inside tempdb by default.
    Then y should we write use tempdb statement before creating temp tables?


    No matter which database is the current context, the #temptable is create in tempdb.

    The database engine attaches a long row of underscores (_________), then a serial number, so each user has his/her own copy of the #temptable. Therefore #temptables are multi-user ready. Demo follows.

    /***** CONNECTION 1 ************/
    
    CREATE TABLE #Product (
    ProductID int identity(1,1) primary key,
    Name varchar(64) unique,
    ModifiedDate datetime default (getdate()));
    
    -- Table created
    -- dbo.#Product____________________________________________________________________________________________________________000000000026
    
    
    /***** CONNECTION 2 ************/
    
    CREATE TABLE #Product (
    ProductID int identity(1,1) primary key,
    Name varchar(64) unique,
    ModifiedDate datetime default (getdate()));
    
    -- Table created
    -- dbo.#Product____________________________________________________________________________________________________________000000000027
    

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server


    • Marked as answer by KJian_ Wednesday, February 10, 2010 7:52 AM
    • Edited by Kalman TothModerator Friday, October 19, 2012 12:36 AM
    Wednesday, February 03, 2010 9:44 AM
    Moderator

All replies

  • i just noticed that , no matter which database is active in the query window, when u create a #table, it is created inside tempdb by default.
    Then y should we write use tempdb statement before creating temp tables?


    There is no rule that we need to write use tempdb statement before creating temp tables.

    Also you can't use "USE Database" statement in storedproc
    Regards, Balwant.
    Wednesday, February 03, 2010 6:25 AM
  • Hi

    Hi,
    We can create local & global temporary table. Temporay Tables are creating in Tempdb
    Local temporary table only visible current session.
    Global temporary table visible accross all session.
    Local Temporary table:
    Syntax:

    CREATE TABLE #TEMP
    (
    COL1 INT,
    COL2 VARCHAR(30),
    COL3 DATETIME DEFAULT GETDATE()
    )
    GO


    Here '#' means local temporary table.

    Limitation:
    1.One cannot create another temporary table with the same name in the same session.

    Global temporary table:
    CREATE TABLE ##TEMP_GLOBAL
    (
    COL1 INT,
    COL2 VARCHAR(30),
    COL3 DATETIME DEFAULT GETDATE()
    )
    GO

    Here '##' means global temporary table


    if u need to create temp table in local db use this below query

    WITH My_temp (nam, id) AS
    (
    SELECT name,id FROM test
    )
    SELECT * FROM My_temp


    Manigandan-DBA , Mark as Answer if it helps!
    sqlserver-manigandan.blogspot.com
    • Marked as answer by KJian_ Wednesday, February 10, 2010 7:53 AM
    Wednesday, February 03, 2010 6:28 AM
  • You'll notice I prefixed the table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table.

    Temporary tables are created in tempdb. If you run this query:

    CREATE TABLE #Yaks (
    YakID int,
    YakName char(30) )
    
    select name
    from tempdb..sysobjects 
    where name like '#yak%'
    
    drop table #yaks
    Manigandan-DBA , Mark as Answer if it helps!
    sqlserver-manigandan.blogspot.com
    Wednesday, February 03, 2010 6:33 AM
  • "This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped."

    how to close/end the session?

    Wednesday, February 03, 2010 6:37 AM
  • If two different users both create a #Temp table each will have their own copy of it. The exact same code will run properly on both connections. Any temporary table created inside a stored procedure is automatically dropped when the stored procedure finishes executing. If stored procedure A creates a temporary table and calls stored procedure B, then B will be able to use the temporary table that A created. It's generally considered good coding practice to explicitly drop every temporary table you create.  If you are running scripts through SQL Server Management Studio or Query Analyzer the temporary tables are kept until you explicitly drop them or until you close the session.

    Wednesday, February 03, 2010 6:42 AM
  • They are not visible by session but they are visible by connection.

    So if you have created temp table in query editor window it will dropped when you close that query window.

    if you have created temp table in stored proc then it scope is only for that storedproc.

    Failure in Life is failure to try...
    Wednesday, February 03, 2010 6:44 AM
  • There is no need write "use tempdb" when creating temp tables because they will be **ALWAYS** created in tempdb; there is no other location they can land in

    For stored procedures , Local Temp tables will be dropped when the execution finishes ; that's why you don't need a "drop table #table" at end of SP

    HTH

    Please mark as answer if you think this answers your questions
    • Marked as answer by KJian_ Wednesday, February 10, 2010 7:52 AM
    Wednesday, February 03, 2010 9:25 AM
  • i just noticed that , no matter which database is active in the query window, when u create a #table, it is created inside tempdb by default.
    Then y should we write use tempdb statement before creating temp tables?


    No matter which database is the current context, the #temptable is create in tempdb.

    The database engine attaches a long row of underscores (_________), then a serial number, so each user has his/her own copy of the #temptable. Therefore #temptables are multi-user ready. Demo follows.

    /***** CONNECTION 1 ************/
    
    CREATE TABLE #Product (
    ProductID int identity(1,1) primary key,
    Name varchar(64) unique,
    ModifiedDate datetime default (getdate()));
    
    -- Table created
    -- dbo.#Product____________________________________________________________________________________________________________000000000026
    
    
    /***** CONNECTION 2 ************/
    
    CREATE TABLE #Product (
    ProductID int identity(1,1) primary key,
    Name varchar(64) unique,
    ModifiedDate datetime default (getdate()));
    
    -- Table created
    -- dbo.#Product____________________________________________________________________________________________________________000000000027
    

    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server


    • Marked as answer by KJian_ Wednesday, February 10, 2010 7:52 AM
    • Edited by Kalman TothModerator Friday, October 19, 2012 12:36 AM
    Wednesday, February 03, 2010 9:44 AM
    Moderator