Table Value Variables
Published: 3rd May 2013
As with single value variables, these are very simple and powerful objects which are also only active and present during the execution of your code. They are automatically dropped and tidied up once execution finishes.

However, in contrast to single value variables, these can hold multiple values and work in the same way as temporary and normal tables. The main differences between these and temporary tables are that you can only place one index on the table and that is a unique (and / or) clustered index.

These are created in a unique way which crosses the single variable declaration with a table create statement:

declare @myTable table
(
  
id int not null,
  
myData varchar(100) not null
)


As mentioned, these act in the same way as tables and therefore you can do most everything that you would expect, from nullable fields to identity columns to default values:

declare @myTable table
(
  
id int identity(1, 1) not null,
  
myData varchar(100) null,
  
myDate datetime default(current_timestamp)
)


These are also populated in much the same way as you would a normal table. The only difference being that you cannot use SELECT INTO, you must declare the table first and use INSERT INTO:

declare @myTable table
(
  
id int identity(1, 1) not null,
  
myData varchar(100) null,
  
myDate datetime default(current_timestamp)
)
insert into @myTable(myData) select 'Data'
insert into @myTable(myData, myDate) values('Data2', '2001-01-01')

select *
from @myTable


As these tables are similar to both normal and temporary tables they also respond to joins, updates, deletes etc. as you would expect. I’ll not put examples, but feel free to investigate yourself and verify these if you wish.

Lastly I’ll just put up a quick example showing the different types of variable (single and table) interacting within a while loop to populate a simple table of dates. You’ll see how it makes the code quite neat yet powerful:

declare @counter int = 1
declare @table table
(
  
myDate date
)
while @counter <= 10
begin
   insert into
@table
  
select CURRENT_TIMESTAMP - @counter

  
set @counter += 1
end

select
myDate
from @table


Comments:
NB: Comments will only appear once they have been moderated.