Share via

  • Article

Using and Modifying Instances of User-defined Types

You can work with user-defined types in the following ways:

  • Creating tables with user-defined type columns

  • Inserting and modifying user-defined type column values

  • Using a user-defined type as a variable or parameter

Creating Tables That Have User-defined Type Columns

You can create a table that has user-defined type columns by providing a column name and referencing the type name. This is similar to the way you create columns that are made up of system-based data types or alias types. To create a column on a common language runtime (CLR) user-defined type, you must have REFERENCES permission on the type.

To create a table with user-defined type columns

Inserting and Modifying User-defined Type Column Values

You can insert and modify column values and change the values of user-defined type variables and parameters.

Note

User-defined types cannot be modified after they are created, because changes could invalidate data in the tables or indexes. To modify a type, you must either drop the type and then re-create it, or issue an ALTER ASSEMBLY statement by using the WITH UNCHECKED DATA clause. For more information, see ALTER ASSEMBLY (Transact-SQL).

You can insert or modify values for user-defined type columns by doing the following:

  • Supplying a value in a SQL Server system data type, as long as the user-defined type supports implicit or explicit conversion from that type. The following example shows how to update a value in a column of user-defined type Point by explicitly converting from a string:

    UPDATE Cities
SET Location = CONVERT(Point, '12.3:46.2')
WHERE Name = 'Anchorage'

  • Invoking a method, marked as a mutator, of the user-defined type, to perform the update. The following example invokes a mutator method of type point called SetXY that updates the state of the instance of the type:

    UPDATE Cities
SET Location.SetXY(23.5, 23.5)
WHERE Name = 'Anchorage'

    Note

    SQL Server returns an error if a mutator method is invoked on a Transact-SQL null value, or if a new value produced by a mutator method is null.

  • Modifying the value of a property or public field of the user-defined type. The expression that supplies the value must be implicitly convertible to the type of the property. The following example modifies the value of property X of user-defined type point:

    UPDATE Cities
SET Location.X = 23.5
WHERE Name = 'Anchorage'

    To modify different properties of the same user-defined type column, issue multiple UPDATE statements, or invoke a mutator method of the type.

The following example inserts values of type Point into the table:

INSERT INTO Cities (Name, Location)
VALUES ('Anchorage', CONVERT(Point, '23.5, 23.5'))

To insert a user-defined type value into a table or view

UPDATE (Transact-SQL)

Using a User-defined Type As a Variable or Parameter

You can declare a Transact-SQL variable, or the parameter of a Transact-SQL or Microsoft .NET Framework function or procedure, to be of a user-defined type. The following rules apply:

  • You must have EXECUTE permission on the type.

  • If you create a function, stored procedure, or trigger that contains a user-defined type declaration with schema-binding, you must have REFERENCES permission on the type.

If you use a one-part name, SQL Server will look up the type reference in the following order:

  1. The schema of the current user in the current database.

  2. The schema of the dbo in the current database.

  3. The system native type-space.

To declare a user-defined type as a Transact-SQL variable or parameter of a function or procedure

Restrictions on Using User-defined Types

When you are creating and working with user-defined types, consider the following:

  • You cannot define length, scale, precision, or other metadata when you create a user-defined type in SQL Server.

  • A column, variable, or parameter cannot be declared to be of a user-defined type that is defined in another database. For more information, see Using User-defined Types Across Databases.

  • To determine whether a user-defined type is identical to one previously defined, do not compare the type ID, because this is valid only for the life of the user-defined type and may be reclaimed. Instead, compare the CLR type name, the four-part assembly name, and the assembly bytes.

  • sql_variant columns cannot contain instances of a user-defined type.

  • User-defined types cannot be used as default values in CLR procedures, functions or triggers, or in partition functions.

  • User-defined types cannot be referenced in computed columns of table variables.