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

• CREATE TABLE (Transact-SQL)

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.

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

• INSERT (Transact-SQL)
• To update 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

• DECLARE @local\_variable (Transact-SQL)

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.

See Also

Concepts

Working with CLR User-defined Types

Other Resources

CLR User-Defined Types

Help and Information

Getting SQL Server 2005 Assistance

Change History