Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

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

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

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

NoteNote

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'
    
    NoteNote

    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)

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

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.

Community Additions

ADD
Show:
© 2015 Microsoft