In this session i am going to explain you about Table Variable in SQL Server.
Introduction:
Table Variable introduced with SQL SERVER 2000 as an alternate for Temporary tables.
We can use Table Variable to store set of records temporally. Unlike other data type table varibale can not use as an input or output parameter like other data types. As we use variables in sql server to store some values with different data type in the same way we can use Table Variable for storing set of record that exists only for current scope. Table Variables are declared into the current batch or stored procedure with the Declare Statement
Here is the syntax for declaring Table Variable.
DECLARE @tablevariablename AS TABLE ( Col1 datatype, ….. Coln datatype )
You can see the difference between declaring a table variable and creating a table.
Scope of Table Variable:
Table Variable is scoped to the stored procedure, batch, or user-defined function just like any local variable you create with a DECLARE statement. The variable will no longer exists after the scope of the Stored Procedure, batch or User-Defined function.
Performance:
Because of limited scope table variable uses fewer resources than temporary table. A temporary table inside stored procedure requires additional recompilation whereas the table variable does not require this additional recompilation of stored procedure.
Let’s start by creating a simple Employee table here is the script.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Employee]( [ID] [int] NOT NULL, [Name] [varchar](100) NULL, [Address] [varchar](100) NULL, [ContactNo] [varchar](50) NULL, PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO
Insert some dummy records here is the script:
INSERT INTO [dbo].[Employee]([ID],[Name],[Address],[ContactNo])VALUES(1,'Vinod Palne','Varanasi','9282899090'); INSERT INTO [dbo].[Employee]([ID],[Name],[Address],[ContactNo])VALUES(2,'Gaurav Mishra','Varanasi','9282899090');
You can see the Employee table after inserting record
In this example I am declaring a table variable and I will copy all the records from employee table.
Here is the script for declaring @tblEmployee.
DECLARE @tblEmploye AS TABLE ( ID INT, Name VARCHAR(100), Address VARCHAR(100), ContactNo VARCHAR(50) )
Now we have to copy records from EmployeeTable to @tblEmployee. Here is the script:
DECLARE @tblEmploye AS TABLE ( ID INT, Name VARCHAR(100), Address VARCHAR(100), ContactNo VARCHAR(50) ) INSERT INTO @tblEmploye (ID,Name,Address,ContactNo) SELECT ID,Name,Address,ContactNo FROM Employee SELECT * FROM @tblEmploye Let’s perform an update operation on table variable DECLARE @tblEmploye AS TABLE ( ID INT, Name VARCHAR(100), Address VARCHAR(100), ContactNo VARCHAR(50) ) INSERT INTO @tblEmploye (ID,Name,Address,ContactNo) SELECT ID,Name,Address,ContactNo FROM Employee UPDATE @tblEmploye SET Name='Dheeraj Gupta' WHERE ID=1 SELECT * FROM @tblEmploye
Let’ perform delete operation on table variable
DECLARE @tblEmploye AS TABLE
(
ID INT,
Name VARCHAR(100),
Address VARCHAR(100),
ContactNo VARCHAR(50)
)
INSERT INTO @tblEmploye (ID,Name,Address,ContactNo)
SELECT ID,Name,Address,ContactNo FROM Employee
DELETE FROM @tblEmploye WHERE ID=1
SELECT * FROM @tblEmploye
Like!! I blog quite often and I genuinely thank you for your information. The article has truly peaked my interest.
Thanks so much for the blog post.
Thank you ever so for you article post.
Very good article! We are linking to this particularly great content on our site. Keep up the great writing.
Gosh This has been an extremely wonderful post. Thank you for providing these details.
Thank you for the good writeup. It in fact was a amusement account it.
Look advanced to far added agreeable from you! However, how could we communicate?
You can definitely see your skills in the work you
write. The world hopes for even more passionate writers like you who aren’t afraid
to mention how they believe. All the time go after your heart.
The problems occurring with the outdated systems similar to air leaks and noise will be now fastened in a comparatively inexpensive manner. Aarika Giulio Granville
Does your website have a contact page? I’m having a tough time locating it but,
I’d like to shoot you an email. I’ve got some
ideas for your blog you might be interested in hearing. Either way,
great website and I look forward to seeing it grow over time.
Piece of writing writing is also a fun, if you be acquainted with then you can write
or else it is difficult to write.
Good day! I could have sworn I’ve been to your blog before but after going
through a few of the articles I realized it’s new to me.
Regardless, I’m definitely happy I found it and I’ll be book-marking it and checking back regularly!
I’ve recently started a site, the info you provide on this web site has helped me greatly. Thank you for all of your time & work. Dionis Akim Lefton
When someone writes an article he/she maintains the image of a
user in his/her mind that how a user can know it. Thus that’s why this
article is great. Thanks!
Thanks a bunch for sharing this with all folks you actually know what you’re speaking about!
Bookmarked. Please additionally consult with my website =).
We may have a hyperlink change contract between us