1

I would like to know where is the table variable created in a computer?

Let's say I declare a table variable as @emp in SQL server, can someone please help to understand where is a table variable created?

Saikiran P.R
  • 13
  • 5
  • please review this answer. [When a variable is declared in a T-SQL stored procedure, is it kept in memory or tempdb?](https://stackoverflow.com/questions/9728775/when-a-variable-is-declared-in-a-t-sql-stored-procedure-is-it-kept-in-memory-or) – Razmik Ghookas Mar 30 '19 at 18:20

2 Answers2

0

table variables are created within tempdb. refer https://learn.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database?view=sql-server-2017

tempdb is stored on disk, and you can check for the file location by right clicking on 

[yourserver] > Databases > System Databases > tempdb

In the properties window, you can find the physical file location in the Files tab.

Saharsh
  • 750
  • 7
  • 18
  • This is not always the case. It could reside memory(memory-optimized filegroup) https://stackoverflow.com/a/55435833/5070879 – Lukasz Szozda Mar 30 '19 at 21:33
0

Table variables are alternatives of temporary tables which store a set of records.

Table variable (@emp) is created in the memory. Whereas, a Temporary table (#temp) is created in the tempdb database. Note:- if there is a memory pressure the pages belonging to a table variable may be pushed to tempdb.

The syntax of a table variable is shown below: 

Declare @emp Table
(
            [EmpID] [int] NULL,
            [EmpName] [varchar](30) NULL,
)

Inserting a value into the table variable:

Declare @emp Table
(
            [EmpID] [int] NULL,
            [EmpName] [varchar](30) NULL,

)

INSERT INTO @emp (EmpID, EmpName) values (1, 'Rohatash')

Select * from @emp
THE LIFE-TIME LEARNER
  • 1,476
  • 1
  • 8
  • 18