Defining UDT Tables and Columns

Once the assembly containing the user-defined type (UDT) definition has been registered in a Microsoft SQL Server database, it can be used in a column definition.

Creating Tables with UDTs

There is no special syntax for creating a UDT column in a table. You can use the name of the UDT in a column definition as though it were one of the intrinsic SQL Server data types. The following CREATE TABLE Transact-SQL statement creates a table named Points, with a column named ID, which is defined as an int identity column and \ the primary key for the table. The second column is named PointValue, with a data type of Point. The schema name used in this example is dbo. Note that you must have the necessary permissions to specify a schema name. If you omit the schema name, the default schema for the database user is used.

CREATE TABLE dbo.Points 
(ID int IDENTITY(1,1) PRIMARY KEY, PointValue Point)

Creating Indexes on UDT Columns

There are two options for indexing a UDT column:

• Index the full value. In this case, if the UDT is binary ordered, you can create an index over the entire UDT column by using the CREATE INDEX Transact-SQL statement.
• Index UDT expressions. You can create indexes on persisted computed columns over UDT expressions. The UDT expression can be a field, method, or property of a UDT. The expression must be deterministic and must not perform data access.

For more information, see CLR User-Defined Types and CREATE INDEX (Transact-SQL).

請參閱

其他資源

Working with User-Defined Types in SQL Server

說明及資訊

取得 SQL Server 2005 協助