User-Defined Table Types (SQL 2008)

In SQL Server 2008, a user-defined table type is a user-defined type that represents the definition of a table structure. You can use a user-defined table type to declare table-valued parameters for stored procedures or functions, or to declare table variables that you want to use in a batch or in the body of a stored procedure or function.

To create a user-defined table type, use the CREATE TYPE statement. To ensure that the data in a user-defined table type meets specific requirements, you can create unique constraints and primary keys on the user-defined table type.

Restrictions
User-defined table types have the following restrictions:

- A user-defined table type cannot be used as a column in a table or a field in a structured user-defined type.

- Alias types based on a user-defined table type The [NOT FOR REPLICATION] option is not allowed.

- CHECK constraints require a computed column to be persisted.

- The primary key on computed columns must be PERSISTED and NOT NULL.

- A nonclustered index cannot be created on a user-defined table type unless the index is the result of creating a PRIMARY KEY or UNIQUE constraint on the user-defined table type. (SQL Server enforces any UNIQUE or PRIMARY KEY constraint by using an index.)

- A DEFAULT value cannot be specified in the definition of a user-defined table type.

- The user-defined table type definition cannot be modified after it is created.

- User-defined functions cannot be called within the definition of computed columns of a user-defined table type.

Creating a user-defined table type
USE AdventureWorks;GO
/* Create a user-defined table type */
CREATE TYPE LocationTableType AS TABLE
(
LocationName VARCHAR(50), CostRate INT
);GO