How to Create a Memory Table in SQL Server

The following simple SQL Script Explains, How to create a memory table, insert, delete, update data to memory table

-- Memory Table Definition

Declare @Memory_Table Table

(

EmpID int,

EmployeeName varchar(10)

)

-- Insert Data to Memory Table

Insert into @Memory_Table(EmpID,EmployeeName) Values(1,'Babu')

Insert into @Memory_Table Values(2,'Ramesh')

Insert into @Memory_Table Values(3,'Mani')

Insert into @Memory_Table Values(4,'Varatha')

Insert into @Memory_Table Values(5,'Kumar')

-- retrieve Data from Memory Table

select * from @Memory_Table

-- delete data from Memory Table

delete from @Memory_Table where EmpID=1

-- retrieve Data from Memory Table

select * from @Memory_Table

-- update the Data in Memory Table

Update @Memory_Table Set EmpID=EmpID - 1

-- retrieve Data from Memory Table

select * from @Memory_Table

We can do the same all the operation of real table on memory table except drop operation.

When I tried to drop table, I got the following error

-- Drop Memory Table

drop table @Memory_Table

Msg 102, Level 15, State 1, Line 31

Incorrect syntax near '@Memory_Table'.

we no need drop, it will be alive as long as query lives, once completed it will be wipeout from memory