Table Types
Published: Oct 09, 2016
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.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

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

Kevin Urquhart
SQL Server Consultant, London



Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron