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