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.
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.
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.
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.
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.
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