Share via

  • Article

User-Defined Table Types

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.

Note

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.

Note

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

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.