{"id":259,"date":"2012-12-31T12:39:34","date_gmt":"2012-12-31T12:39:34","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2012\/12\/31\/interesting-observation-declaring-table-variables-impact-on-temp-db\/"},"modified":"2012-12-31T12:39:34","modified_gmt":"2012-12-31T12:39:34","slug":"interesting-observation-declaring-table-variables-impact-on-temp-db","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2012\/12\/31\/interesting-observation-declaring-table-variables-impact-on-temp-db\/","title":{"rendered":"Interesting Observation \u2013 Declaring Table Variables impact on Tempdb"},"content":{"rendered":"

Following was an intresting observation when creating table variables.
\n1. Table variables are created in tempdb.
\n2. Table variables are created in tempdb even before the declare table is called.<\/strong><\/p>\n

Steps to find that the Table variables are created in tempdb:<\/strong>
\n1. Restart your local SQL Server instance. (So that the tempdb gets cleared)
\n2. Connect to the SQL Server in SQL Server management studio and check if there are any records in sys.objects in tempdb.
\n[sql]
\nSELECT * FROM tempdb.sys.tables
\n[\/sql]
\nYou will find there are 0 records(ideally)
\n3. In a new query window run the below script.
\n[sql]
\nUSE MASTER
\nGO<\/p>\n

DECLARE @TableVariable TABLE (ID INT,VALUE VARCHAR(100))<\/p>\n

INSERT INTO @TableVariable
\nselect 1,’A’<\/p>\n

WAITFOR DELAY ’00:02:00′
\n[\/sql]
\n4. While the above code is getting executed run the below script in other query window.
\n[sql]
\nSELECT * FROM tempdb.sys.tables
\n–Take objectid from the above resultset
\nSELECT * FROM tempdb.sys.columns where OBJECT_ID = 117575457
\n[\/sql]<\/p>\n

Result:<\/strong> It can be found that the tempdb.sys.tables and tempdb.sys.columns has records for the table variable. This proves that the table variable is created in tempdb.<\/strong><\/p>\n

Steps to find that the Table variables are created in tempdb even before the declare table is called:<\/strong>
\n1. Restart your local SQL Server instance. (So that the tempdb gets cleared)
\n2. Connect to the SQL Server in SQL Server management studio and check if there are any records in sys.objects in tempdb.
\n[sql]
\nSELECT * FROM tempdb.sys.tables
\n[\/sql]
\nYou will find there are 0 records(ideally)
\n3.In a new query window run the below script.<\/p>\n

[sql]
\nSELECT * FROM tempdb.sys.tables<\/p>\n

DECLARE @TableVariable TABLE (ID INT,VALUE VARCHAR(100))<\/p>\n

INSERT INTO @TableVariable
\nselect 1,’A’
\n[\/sql]<\/p>\n

Result:<\/strong> It can be found that there is a record in the tempdb.sys.tables even before the DECLARE Statement is executed. This proves that the table variables are created in tempdb even before the declare table is called.<\/strong><\/p>\n

Wishing all the blog readers advanced happy new year. \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"

Following was an intresting observation when creating table variables. 1. Table variables are created in tempdb. 2. Table variables are created in tempdb even before the declare table is called. Steps to find that the Table variables are created in tempdb: 1. Restart your local SQL Server instance. (So that the tempdb gets cleared) 2.…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[640,2,3,1789],"tags":[2165],"class_list":["post-259","post","type-post","status-publish","format-standard","hentry","category-interview-questions","category-sql-server-variables","category-sqlserverpedia-syndication","category-t-sql-code-examples","tag-table-variables-are-created-in-tempdb"],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/259","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=259"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/259\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=259"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=259"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=259"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}