Using Table Variables Instead of Temporary Tables in SQL Server

Microsoft introduced table variables in SQL Server. Table variables are used instead of temporary tables. Similar to temp tables, we can use table variables to store data that we used to store in temp tables.

The following statement is used to declare a Table variable, which is pretty similar to a CREATE TABLE statement in SQL.

Example:

Declare @customersvar  Table( Id int  identity(1,1), customerID   nchar(5)  NOTNULL, Name   varchar(50) , Address  varchar(max) , PhoneNo  varchar(50) )

We can write the following INSERT INTO statement to insert values in the table variable.

Insert into @customervar Table (customerID, Name, Address,  PhoneNo )

We can write the following SELECT statement to populate the table variable.

SELECT * FROM @customersvar

And to populate the table variables first fifty values, you can write the following SELECT statement

SELECT TOP 50 * FROM @customersvar

When we create a temporary table (#TABLE) , which  physically creates  the table in tempdb so it is creates  burden . When we create a table variable which is creating  in memory so it's much faster.

And we can use table variables when creating batches, stored procedures, and user-defined functions (UDFs).

And also you can UPDATE records in your table variable as well as DELETE records.

Example:

UPDATE  @customersvar  SET Name  = 'Reema' WHERE  customerID = 158 DELETE  FROM  @customersvar   WHERE  customerID = 1020