Table Types
Published: 20th January 2015
This was something which I used recently within my own personal work at home because I was writing a stored procedure which involved regularly creating tables with the same structure.

In the end I got annoyed with copy and paste and decided to use a Table Type as a template.

As the last line implies, a Table Type can be thought of as a table template. You can define the structure of the type and from then on you can re-use that at will.

i’m going to go straight into code with this one and create my table type which I’ll be calling “demoType”.

You simply have to create a new Type and tell SQL Server that it’s a table. Then proceed to your declaration:

if exists
select name
from sys.table_types
where name = 'demoType'
       drop type

create type
demoType as table
id int identity,
myDate smalldatetime,
myValue smallint,
misc varchar(10)

That’s literally it. It’s that simple.

Now you can just declare a new table using this template and fill it with data without having to keep copying and pasting column lists:

declare @newTable demoType

insert into @newTable
select current_timestamp, 1, 'a'

select *
from @newTable

It’s that simple.

The other advantage is that if you’re using these throughout your code then if you suddenly find that you needed a column to be a varchar(50) instead of a varchar(45) then you need to make just one change to your Type rather than a lengthy find and replace.
NB: Comments will only appear once they have been moderated.