Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


Table Value Variables
Published: Dec 30, 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


Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.