{"id":44,"date":"2007-12-19T23:21:09","date_gmt":"2007-12-19T21:21:09","guid":{"rendered":"https:\/\/www.sqlinthewild.co.za\/index.php\/2007\/12\/19\/temp-tables-and-table-variables\/"},"modified":"2007-12-19T23:21:09","modified_gmt":"2007-12-19T21:21:09","slug":"temp-tables-and-table-variables","status":"publish","type":"post","link":"https:\/\/www.sqlinthewild.co.za\/index.php\/2007\/12\/19\/temp-tables-and-table-variables\/","title":{"rendered":"Temp tables and table variables"},"content":{"rendered":"<p>I&#8217;ve encountered a fair bit of confusion on various forums as to the differences between temporary tables and table variables. As a quick article (I&#8217;m knee-deep in some AI stuff at the moment) I thought I&#8217;d quickly go over some points on temp tables and table variables.<\/p>\n<p><strong>Temporary Tables<\/strong><\/p>\n<ul>\n<li>Created using the Create table syntax, preceding the table name with a&#8217;#&#8217; for a local temp table and &#8216;##&#8217; for a global temp table<\/li>\n<li>Allocated storage space within the TempDB database and entered into the TempDB system tables <sup>1<\/sup><\/li>\n<li>The table&#8217;s actual name is the name is was created with, a large number of underscores and a hash value, to prevent object name collisions if two connections create a temp table with the same name<\/li>\n<li>Can have a primary key, defaults, constraints and indexes (however the names of these are not hashed, possibly leading to duplicate object errors for constraints and defaults)<\/li>\n<li>May not have triggers.<\/li>\n<li>Foreign keys are permitted, but are not enforced<\/li>\n<li>Have column statistics kept on them. The algorithm for determining when to update is different to permanent tables<\/li>\n<li>Exist until they are dropped, or the connection closes.<\/li>\n<li>Are visible in any child procedures called from the one where the table was created. Are not visible to parent procedures<\/li>\n<li>Are not persisted to disk unless there is memory pressure, or the table is too large to fit in the data cache<\/li>\n<\/ul>\n<p><!--more--><strong>Table Variables<\/strong><\/p>\n<ul>\n<li>Created with a Declare statement, prefixing the table name with &#8216;@&#8217;, like all other variables.<\/li>\n<li>Allocated storage space within the TempDB database and entered into the TempDB system tables<\/li>\n<li>The table variable&#8217;s name within TempDB starts with a #, followed by a hex string.<\/li>\n<li>Can have a primary key and defaults. May not have constraints or indexes<\/li>\n<li>May not have triggers or foreign keys.<\/li>\n<li>Do not have column statistics maintained on them <sup>2<\/sup><\/li>\n<li>Exist while they are in scope and are automatically dropped when they go out of scope.<\/li>\n<li>Are not visible in any procedures other than the one they were created in<\/li>\n<li>Are not persisted to disk unless there is memory pressure, or the table is too large to fit in the data cache<\/li>\n<li>Cannot be truncated<\/li>\n<li>Do not take part in transactions. Data modification done to  a table variable within a transaction will remain if the transaction is rolled back <sup>3<\/sup><\/li>\n<\/ul>\n<p>(1)  As seen with the following code<\/p>\n<pre>select * from tempdb.sys.tables\ngo<\/pre>\n<pre>DECLARE @Test1 TABLE (id int)\nSELECT * FROM tempdb.sys.tables\nGOCREATE TABLE #Test2 (\nid int\n\nSELECT * FROM tempdb.sys.tables\nGO\n\ndrop table #Test2<\/pre>\n<p>(2) Because of this, the query optimiser has no idea, when a query runs, how many rows are in a table variable. It estimates 1. If there are a lot of rows in the table variable, this can result in a very poor plan and very poor query performance.<\/p>\n<p>(3)  As seen with the following<\/p>\n<pre>DECLARE @Test1 TABLE (id int)<\/pre>\n<pre>BEGIN TRANSACTION\nINSERT INTO @Test1 VALUES (1)\nROLLBACK TRANSACTION<\/pre>\n<pre>SELECT * FROM @Test1  -- 1 row<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>I&#8217;ve encountered a fair bit of confusion on various forums as to the differences between temporary tables and table variables. As a quick article (I&#8217;m knee-deep in some AI stuff at the moment) I thought I&#8217;d quickly go over some&#8230; <a class=\"read-more-button\" href=\"https:\/\/www.sqlinthewild.co.za\/index.php\/2007\/12\/19\/temp-tables-and-table-variables\/\">(Read more)<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[15,17],"tags":[],"class_list":["post-44","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-t-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p7h6n-I","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/44","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/comments?post=44"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/posts\/44\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/media?parent=44"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/categories?post=44"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlinthewild.co.za\/index.php\/wp-json\/wp\/v2\/tags?post=44"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}