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.
RestrictionsUser-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.
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 typeThe 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
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
Removed the following statement from Restrictions: A DEFAULT value cannot be specified in the definition of a user-defined table type.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4