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
|