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'
)
begin
       drop type
demoType
end

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


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.
Comments:
NB: Comments will only appear once they have been moderated.