User-Defined Data Type (Table)

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>[;]