Ask Learn
Preview
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign inUser-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 AdventureWorks2008R2;
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