Table Variable in SQL Server store a set of records like SQL tables, and these are best alternative to Temp Tables. Like Local Variables, Table variable scope is limited to User Defined Functions, or Stored procedures. Table variable is very fast when compared to temporary tables, and it is recommended to use this for less amount of data.
Table Variable in SQL Server Syntax
The syntax of the SQL Server Table variable is similar to the Create Table Statement
|
1 2 3 4 5 6 7 |
DECLARE @Table_Variable_Name TABLE ( Column_Name1 Data_Type(Size) [NULL | NOT NULL], Column_Name2 Data_Type(Size) [NULL | NOT NULL], … Column_NameN Data_Type(Size) [NULL | NOT NULL] ); |
TIP: You cannot use a table variable as either Input, or an Output Parameter.
Table Variable in SQL Server Example
In this example we will show you, How to Create a table variable, How to insert new records into it, and how to select records from the table variable.
SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- Creating SQL Server Table Variable DECLARE @TempTableVariable TABLE ( [CustomerKey] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](50) NULL, [LastName] [varchar](50) NULL, [BirthDate] [date] NULL, [EmailAddress] [nvarchar](50) NULL, [Yearly Income] [money] NULL, [Profession] [nvarchar](100) NULL ) -- Inserting Values in SQL Table Variable INSERT INTO @TempTableVariable VALUES ('Imran', 'Khan', '10-08-1985', 'abc@abc.com', 15900, 'Skilled Professional') ,('Doe', 'Lara', '10-08-1985', 'xyz@abc.com', 15000, 'Management') ,('Ramesh', 'Kumar', '10-08-1985', 'mnop@abc.com', 65000, 'Professional') -- Selecting Values from SQL Server Table Variable SELECT * FROM @TempTableVariable |
OUTPUT
As you can see from the above screenshot, we are executing all the statements together. let me show you, what will happen when we execute them independently.
OUTPUT
There won’t be any problem to declare a table variable
OUTPUT
Hope you understood the scope of a table variable.
NOTE: You don’t need any DELETE, or DROP statement to drop the table variable. Database Engine will automatically delete when it exit from the scope
Thank You for Visiting Our Blog
Leave a Reply