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.
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
Pointby 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
SetXYthat updates the state of the instance of the type:
UPDATE Cities SET Location.SetXY(23.5, 23.5) WHERE Name = 'Anchorage'
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
Xof 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
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:
- The schema of the current user in the current database.
- The schema of the dbo in the current database.
- 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.
Help and Information
5 December 2005