Using @@ROWCOUNT and TABLE Variables for Database Interactions with Transact-SQL - A brief introduction to TABLE variables in T-SQL programming
(Page 3 of 4 )
Up to now, we have seen variables of standard data types like numeric, varchar, and so on. Now we are going to concentrate on TABLE variables. A TABLE type of variable is very similar to a variable which can hold a table of data (in the form of rows and columns). These table variables would exist only during the execution of script and will not physically affect existing database structures. In other words, they are completely managed in memory (and sometimes in TempDB as well).
A table variable must be defined with a structure (column specification) based on the rows it may contain. Let's take a look at a sample script which uses a TABLE variable. The following is the code:
use northwind
set nocount on
go
declare @t_emp TABLE
(
EmpID int,
Ename varchar(30)
)
insert into @t_emp(EmpID, Ename)
values (1001, 'Jagadish')
insert into @t_emp(EmpID, Ename)
values (1002, 'Chatarji')
select * from @t_emp
go
Let us try to understand the above script line by line.
declare @t_emp TABLE
(
EmpID int,
Ename varchar(30)
)
The above statement simply defines a new variable named @t_emp which contains two columns (EmpID and Ename) of storage. This table variable exists only during the execution of the script and automatically vanishes (it is erased from memory) once the script execution is complete. Going further, we have the following:
insert into @t_emp(EmpID, Ename)
values (1001, 'Jagadish')
insert into @t_emp(EmpID, Ename)
values (1002, 'Chatarji')
The above two are very similar to the traditional INSERT statements, but are focused on inserting into the @t_emp table variable. Going further, we have the following:
select * from @t_emp
Once all the rows are inserted into the table variable, I am simply displaying all the information back to the user in the form of a real table!
Primary key and Identity columns in TABLE variables
Just like database tables, we can enforce TABLE variables with Primary Key, Unique, Identity, NOT NULL etc. types of constraints. Please understand, however, that not all types of constraints will work with TABLE variables.
Let us rewrite the previous script to enforce the "primary key" constraint as follows:
use northwind
set nocount on
go
declare @t_emp TABLE
(
EmpID int primary key,
Ename varchar(30)
)
insert into @t_emp(EmpID, Ename)
values (1001, 'Jagadish')
insert into @t_emp(EmpID, Ename)
values (1001, 'Chatarji')
select * from @t_emp
go
The above script would result in an error with primary key violation, as I am trying to insert two rows with the same employee id.
We can even include identity columns (also called auto number or surrogate key columns) as part of the TABLE variable as follows:
use northwind
set nocount on
go
declare @t_emp TABLE
(
ID int identity(1,1),
EmpID int primary key,
Ename varchar(30)
)
insert into @t_emp(EmpID, Ename)
values (1001, 'Jagadish')
insert into @t_emp(EmpID, Ename)
values (1002, 'Chatarji')
select * from @t_emp
go
The output for the above script would result in three columns, where the first column would contain sequentially generated values starting with 1 and incrementing by 1.
Next: Filling a TABLE variable with existing rows >>
More MS SQL Server Articles
More By Jagadish Chaterjee
