Creating a Table User-Defined
Data Type
Creating a table user-defined data type works as something of a
combination of the classic CREATE TYPE and table variable syntax. The tabular
CREATE TYPE syntax looks like this:
CREATE TYPE
[<schema name>.]<type name>
AS TABLE
(
{<column
name><data type>
[ COLLATE
<collation name>]
[ NULL | NOT NULL
]
[ DEFAULT
<expression>]
[ IDENTITY [ (
<seed>, <increment>)]
[ ROWGUIDCOL ]
[<column constraint> [ ...n ] ]
[<table
constraint>]}
|<computed
column definition>}
)[;]
Long ago, it seemed one address was generally enough for most people.
The majority of systems out there stored a single address for most business
entities they worked with. Today, however, one doesn’t seem to be enough.
Between dealing with companies that have multiple locations, and even
individuals deciding to receive bills at one location, but ship to a different
location, many business entities we work with have multiple addresses. The
AdventureWorks2008 database represents this by separating addresses out into their
own table (Person.Address).We’ve decided that we want to represent this notion
of an address in a consistent way across our systems, so we create our custom
type:
USE
AdventureWorks2008;
GO
CREATE TYPE
Person.Address
AS TABLE(
AddressID int
NULL,
AddressLine1 nvarchar(60)
NOT NULL,
AddressLine2
nvarchar(60) NULL,
City nvarchar(30)
NOT NULL,
StateProvinceID
int NOT NULL,
PostalCode
nvarchar(15) NOT NULL,
SpatialLocation
geography NULL
);
There are a host of items to notice about this script:
Ø
I used the exact name of an existing object in
the database (there is a table called Person.Address). The type can be
considered to be much like the difference between a class and an object— that
is, a type is a definition, and a table is an actual instance of something (though,
the table is not an instance of the type definition the way an object is an
instance of a class).
Ø
The syntax for creating the actual definition is
very similar to the CREATE TABLE syntax.
Ø
The layout maps very closely to the Person.Address
table in order to support moving data between the two relatively easily.
Note that I created my user-defined type with the same name as a table
just to prove the point. I would
not recommend duplicating names in practice, as it is likely to lead to
far more confusion than it is worth.
Let’s further our example just a bit by utilizing our new type:
DECLARE @Address Person.Address;
INSERT INTO @Address
(AddressID,AddressLine1,City,StateProvinceID,PostalCode) VALUES
(1,‘My first address’,‘MyTown’,1,‘21212’),
(1,‘My second address’,‘OtherTown’,5,‘43434’),
(1,‘My third address’,‘MyTown’,1,‘21214’);
SELECT * FROM @Address;
Notice that, with a simple declaration of our Person.Address
user-defined type, we gained access to all
the columns for that tabular type. We’re able to insert rows, and
select them back out:
(3 row(s) affected)
AddressID AddressLine1
----------- --------------------
1 My first address ...
1 My second address ...
1 My third address ...
(3 row(s) affected)
Again, we’ll take a further look at uses for this in Chapter 10 as part
of our table-valued parameter
discussion.
Dropping a User-Defined Type
It’s fairly intuitive, but just to make sure we’ve addressed the point;
all varieties of UDTs are dropped using the very common DROP <object
type> <object name> syntax:
DROP TYPE
[<schema name>.]<type name>[;]
