Results 1 to 16 of 16

Thread: SQL Server Temporary Tables - General Question

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    SQL Server Temporary Tables - General Question

    If this is true:

    Any temporary table created inside a stored procedure is automatically dropped when the stored procedure finishes executing.

    Then why should you do this?:

    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.

    Source: http://www.sqlteam.com/article/temporary-tables
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Server Temporary Tables - General Question

    For the same reason you set object variable to nothing in VB or C#. It's considered good practice to clean up after yourself in just about every programming enviroment and in some it's necessary if you don't want 'ghosts' left floating around.

    There are a few concrete benefits (things get cleaned up quicker so it helps performance) but, IMO, the real benefit is that it forces you to be disciplined as a developer. You'll start to think along the lines of: do I really want to keep this memory space tied until the end of the procedure or could I release it half way through.

    Basically, it's not necessary, but it's desirable.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Temporary Tables - General Question

    Then may I ask you this? My stored procedure with the temporary table was tested over the weekend with a load simulation of multiple users. I am concerned with the statement in post#1 - "If you are running scripts...the temporary tables are kept until you explicitly drop them". If my sproc demonstrated poor performance and was not explicitly dropping the table, could that be the reason? And if it were truly real multiple users, would it have performed better because the tables would've been dropped sooner?

    I am having a hard time seeing where I need to tune my query. It only performed poorly with this load on it, and unless I can simulate the load myself, I won't know if any of my changes are making a difference (such as dropping the query explicitly).

    Thanks.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Server Temporary Tables - General Question

    could that be the reason?
    Yes, particularly given the nature of your bosses load test (which I find dubious).

    I've pretty much covered it in the other thread though so I won't go through it all again here. Atually, it might be worth asking a mod to merge the because the topics have converged.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  5. #5

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Temporary Tables - General Question

    Yeah, that wasn't intentional but I did end up overlapping. I intended this to be more general. Sorry about that.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  6. #6
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: SQL Server Temporary Tables - General Question

    Quote Originally Posted by MMock View Post
    If this is true:

    Any temporary table created inside a stored procedure is automatically dropped when the stored procedure finishes executing.

    Then why should you do this?:

    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.

    Source: http://www.sqlteam.com/article/temporary-tables
    I don't think that applies to global temporary tables (they start with ##). I think they are available until the connection is closed.

  7. #7
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: SQL Server Temporary Tables - General Question

    Just FYI... there are also variable tables. Those are like regular tables but the data is stored in RAM only, making the processing faster in some cases (if you use it properly). Since it's a variable, you can't un-define it, therefore you can't drop the variable table, it will be dropped when your procedure ends.

  8. #8

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server Temporary Tables - General Question

    Right. And I believe those are best used for a small recordset? So if I can possibly be selecting 20,000 records, it's best to out them in a temp table not a variable table. Please comment if I am wrong, but I thought that was what i read.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  9. #9
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: SQL Server Temporary Tables - General Question

    You got it right... for small recordsets, variable table is better, and for big recordsets temp table is better.

  10. #10
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: SQL Server Temporary Tables - General Question

    Ow, and another very usefull thing to know about variable tables, is that variable tables are not included in a transaction. You can use this for debugging your procedure, for example:

    declare @tmp table (........)
    begin transaction
    do some processing
    insert into @tmp values (some debug data)
    do some more processing
    etc...
    rollback transaction
    select * from @tmp // this will display data inserted while in the transaction

  11. #11
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Server Temporary Tables - General Question

    Those are like regular tables but the data is stored in RAM only
    Actually that's not true. I believed it for years and ended up with egg all over my face at a conference. It was particularly annoying as I'd mercilessly drilled it into my team of developers. They never let me hear the end of it

    Table variables and Temp tables can both exist either on disk in TempDB or in memory. The difference is that a Temp Table behaves as a "real" table while a table variable behaves as a variable. So you can index a temp table but you can't index a table variable. Also a table variable is only available in the current scope where as a temp table is available across the entire session. I think the only other difference is how they're handled in transactions which you mentioned. Because a table variable isn't "real" changes to it don't get written to the transaction log, which is why the rollback doesn't have any effect. There are probably other differences I've overlooked.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  12. #12
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    Re: SQL Server Temporary Tables - General Question

    Well, what I know is from reading online articles, and apparently not all of them are accurate

  13. #13
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Server Temporary Tables - General Question

    Yep, that describes exactly where I was right up until the moment the egg hit
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  14. #14
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: SQL Server Temporary Tables - General Question

    The difference is that a Temp Table behaves as a "real" table while a table variable behaves as a variable. So you can index a temp table but you can't index a table variable
    Not exactly true...you can create a primary key column which is implemented through an index.

    Code:
    Declare @VBForums table(pkKey Int Primary Key, Blah VarChar(20))
    
    insert into @VBForums(pkKey,blah) Values(1,'blah')
    insert into @VBForums(pkKey,blah) Values(2,'blah')
    insert into @VBForums(pkKey,blah) Values(3,'blah')
    insert into @VBForums(pkKey,blah) Values(4,'blah')
    insert into @VBForums(pkKey,blah) Values(5,'blah')
    
    select* from @VBForums
    Look at the execution plan for that.

  15. #15
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: SQL Server Temporary Tables - General Question

    Quote Originally Posted by FunkyDexter View Post
    Actually that's not true. I believed it for years and ended up with egg all over my face at a conference. It was particularly annoying as I'd mercilessly drilled it into my team of developers. They never let me hear the end of it

    Table variables and Temp tables can both exist either on disk in TempDB or in memory. The difference is that a Temp Table behaves as a "real" table while a table variable behaves as a variable. So you can index a temp table but you can't index a table variable. Also a table variable is only available in the current scope where as a temp table is available across the entire session. I think the only other difference is how they're handled in transactions which you mentioned. Because a table variable isn't "real" changes to it don't get written to the transaction log, which is why the rollback doesn't have any effect. There are probably other differences I've overlooked.
    You got one out of three

    Nothing personal...when I see positive statements I like to check them out. I didn't try the examples but this link tries to dispel three common myths:

    Table Variables are memory-only: False

    Table Variables cannot be indexed: False -- I knew that off the top of my head and had that example.

    Changes to Table Variables are not logged: False

    Check it out:

    http://sqlserverpedia.com/blog/sql-s...ble-variables/
    Last edited by TysonLPrice; Apr 17th, 2012 at 05:46 AM.

  16. #16
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: SQL Server Temporary Tables - General Question

    Well there you go. Turns out I'm still wearing omelette
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width