SQL Server 2008 Books Online (October 2009)
User-Defined Table Types

Updated: 30 September 2009

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. For more information about how to define a table structure, see CREATE TABLE (Transact-SQL).

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.

For information about the catalog views that are associated with user-defined types, see sys.types and sys.table_types.

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.)
  • 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.
Security

Permissions for user-defined table types follow the object security model for SQL Server by using the following Transact-SQL keywords: CREATE, GRANT, DENY, ALTER, CONTROL, TAKE OWNERSHIP, REFERENCES, EXECUTE, VIEW DEFINITION, and REVOKE.

Bb522526.note(en-us,SQL.100).gifNote:
CONTROL permission on a table type implies all other permissions on the table type.

A schema binding is established when the function in which the DECLARE statement occurs specifies WITH SCHEMABINDING. The REFERENCES permission is required on the user-defined table type when the table type is a parameter in a routine or when SCHEMABINDING is specified. In all other cases, no schema binding is established and REFERENCES permission is not required on the user-defined table type.

To declare a table variable that uses a user-defined table type, EXECUTE permission is required on that user-defined table type.

Bb522526.note(en-us,SQL.100).gifNote:
The CASCADE option is not enforced for user-defined table type permissions because a user-defined table type cannot be embedded in any type definition.
Examples

A. Creating a user-defined table type

The following example shows how to create 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

B. Revoking permissions on a user-defined table type

The following example shows how permissions can be revoked on a specific user-defined table type. The REFERENCES permission is revoked from a user named JoAnna on the user-defined table type CustomerListType that is within the relational schema mySchema in database myDatabase.

USE myDatabase
GO
REVOKE REFERENCES ON TYPE::[mySchema].[CustomerListType] FROM JoAnna
GO
See Also

Concepts

Table-Valued Parameters (Database Engine)

Other Resources

CREATE TYPE (Transact-SQL)
DECLARE @local_variable (Transact-SQL)

Help and Information

Getting SQL Server 2008 Assistance
Change History

Updated content

Removed the following statement from Restrictions: A DEFAULT value cannot be specified in the definition of a user-defined table type.
Tags :


Page view tracker