Advanced SQL tutorial pdf, Are table variables only stored in memory - SQL Server Q&A, Count puzzle, Count(*) VS Count(ColumnName) VS Count(1), Define below transformation in DFD?, Difference between Cached Report and Snapshot Report, Download SQL Questions, Download SQL Server Interview Question in PDF, Download SQL SERVER Interview questions, Download SQL Server Interview questions and answers, download sql server interview questions and answers pdf, download sql server interview questions by Pawan Khowal, download sql server interview questions by Pawan Kumar, download sql server interview questions by Pawan Kumar Khowal, Download T-SQL Interview Questions, Free Download SQL SERVER Interview questions, How to make connection with a FTP server?, How to show "No Data Found" Message to end user?, Is table variable stored in tempdb?, SQL, sql - Count(*) vs Count(1), sql - COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?, SQL Best Practice: count(1) or count(*), SQL Common Interview Questions, SQL Common Interview Questions and answers, SQL count puzzle, SQL FAQ, SQL FAQs, SQL Interview Q & A, SQL Interview Questions, SQL Queries asked in interviews, SQL Questions, SQL Server - General Interview Questions and Answers, SQL SERVER – Watching Table Variable Data in TempDB, SQL Server developer Interview questions and answers, SQL Server developer Interview questions with answers, SQL SERVER Indexes, SQL SERVER Interview questions, SQL SERVER Interview questions & Answers, SQL Server Interview questions and answers, SQL Server Interview Questions and Answers - Free PDF, sql server interview questions and answers for net developers, SQL SERVER Tips, SQL Tips & Tricks, SQL Tips and Tricks, SQL Tricks, T-SQL Output Query | [ The Count Puzzle ], T-SQL Server Interview Questions, Table Variable are created and stored in tempdb, Table Variables are Created in TempDB, Table Variables Are Only in Memory: Fact or Myth, table variables created and held in memory or in tempdb?, Tables Variables ARE Stored in tempdb, where is table variables stored in the database?, where table variables are stored in sql server
SQL Server | Table Variable are stored in TempDB
Today I am going to talk about the most common misconception about the table Variables. Most of the people doesn’t know about where table variables are stored? If you ask them they will say memory, RAM, Main memory or something like that. And more over if you explain them that they are stored in TempDB, they will not agree with you and may go one step further saying you don’t have knowledge about table variables, LOL:). Anyways today I am going to explain you where table variables are stored and how we can verify it.
Example 1 – Here you can easily see what tables you have in your TempDB before table Variable creation and after table variable creation. Please note that I am using 3 part name in the below queries.
-- SELECT * From TEMPDB.sys.tables GO --Create a Table Variable DECLARE @tableVariable TABLE ( Id INT ,Name VARCHAR(20) ) SELECT * From TEMPDB.sys.tables GO --
Example 2 – Physical Location – Here you can see table Variables data in TempDB. Please note that I am using an undocumented DBCC flag. Do not use this on production.
-- DECLARE @Id TABLE ( Id INT ,Name VARCHAR(20) ) INSERT INTO @Id (Id,Name) VALUES (1,'Pawan'), (2, 'Sharlee') DECLARE @fileId INT = 0 , @pageId INT = 0 SELECT DISTINCT @fileId = file_id , @pageId = page_id FROM @Id CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) DBCC TRACEON(3604) DBCC PAGE('TEMPDB', @fileId, @pageId, 3) DBCC TRACEOFF(3604) --
Enjoy! I hope now it is clear that table variables are stored in tempDB. Cheers !!
Pawan Kumar Khowal