แก้ไข

แชร์ผ่าน

Define user-defined type (UDT) tables and columns

Applies to: SQL Server

Once the assembly containing the user-defined type (UDT) definition is registered in a SQL Server database, it can be used in a column definition. For more information, see CREATE TYPE.

Create tables with UDTs

There's 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. 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
);

Create 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 CREATE INDEX.

Related content