Post reply

What is the life time of temporary table?

  • March 22, 2010 at 6:07 am

    #222167

    Can you please give me some details about temporary tables?

    1. If I use in function. Can I call the values outside the function?

    2. Will the temporary table dropped automatically?

    3. Why we use temp tables instead of creating tables? because of database space?

    4. Do I need to give spec for temp table also if i want to create temp table?

    Please help me to know this?

  • March 22, 2010 at 6:18 am

    #1137519

    A temp table is dropped when the connection that created it is closed or when the procedure that it was created in ends.

    You can't create a temp table in a function, it's not permitted. You can declare a table variable.

    You use temp tables not normal tables because normal users should never have create table permissions in the application database. Temp tables don't need create table permissions.

    What do you mean by 'spec of table'? The syntax for creating a temp table is almost the same as for a user table. The table's name is just prefixed with a #.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 22, 2010 at 6:21 am

    #1137520

    Start reading here:

    http://msdn.microsoft.com/en-us/library/ms186986.aspx

    There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

    http://msdn.microsoft.com/en-us/library/ms177399.aspx

    Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when they are no longer used.

    There are two types of temporary tables: local and global. They differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server.

    For example, if you create the table employees, the table can be used by any person who has the security permissions in the database to use it, until the table is deleted. If a database session creates the local temporary table #employees, only the session can work with the table, and it is deleted when the session disconnects. If you create the global temporary table ##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it after you disconnect and after all other sessions are no longer actively using it.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • March 22, 2010 at 6:24 am

    #1137522

    Can I declare a temp table outside the procedure and call it inside the procedure?

    In procedure we can create a temp table?

  • March 22, 2010 at 6:27 am

    #1137527

    If I am creating global temporary table. it will not deleted once i disconnect the server? or if any one using at that time only it will not be deleted?

  • March 22, 2010 at 6:29 am

    #1137530

    chandrasekaran.ganapathy (3/22/2010)

    Can I declare a temp table outside the procedure and call it inside the procedure?

    In procedure we can create a temp table?

    Yes and yes

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 22, 2010 at 6:39 am

    #1137538

    When it will be deleted automatically?

  • March 22, 2010 at 6:52 am

    #1137549

    chandrasekaran.ganapathy

    When it will be deleted automatically?

    From previous posting in this forum

    Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • March 22, 2010 at 7:20 am

    #1137576

    Did you bother to read the links that bitbucket provided? If not, please do so. If you still have questions after reading them, come back and ask.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 22, 2010 at 7:27 am

    #1137586

    chandrasekaran.ganapathy (3/22/2010)

    When it will be deleted automatically?

    Gosh... haven't you stopped to read any of the responses or visit any of the web sites that BitBucket posted?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply