You can invoke a method or retrieve a property of a user-defined type anywhere that a scalar expression can appear. You must have EXECUTE permission on the type. Methods that change the state of data outside an UPDATE statement will be executed, but the changes will be ignored because mutable methods are not allowed in queries.

Strings that are returned by user-defined type methods assume the collation of the database in which the user-defined type was created, regardless of the current database.

Caution:
Methods and properties that are used in SELECT statements must not have side effects. If a method used in a SELECT statement has side effects, the results are not deterministic.

Caution:
When method or property invocations occur against user-defined types over which computed columns are created and the invocation occurs in the context of an operation on the computed column, EXECUTE permissions on the type are not checked. Instead, permissions are checked when the computed column is created.

To invoke a method or retrieve a property of a user-defined type

• SELECT Clause (Transact-SQL)
  

Note:
SQL Server Management Studio returns user-defined type values in binary representation. To return user-defined type values in string or XML format, use CAST or CONVERT.

You can perform implicit and explicit conversion with user-defined types as summarized in the following table.

The following example explicitly converts an instance of user-defined type ComplexNumber to xml and invokes an XQuery expression over it.

DECLARE @c ComplexNumber
SET @c = CONVERT(ComplexNumber, '(1,2i)')
SELECT CAST(@c AS xml).query('//Real/text()')

The following example implicitly converts xml data to an instance of user-defined type ComplexNumber.

DECLARE @x xml, @u ComplexNumber
SET @x = '<ComplexNumber><Real>100</Real><Imaginary>4</Imaginary></ComplexNumber>'
SET @u = @x -- implicit convert
SELECT @u.ToString()

For more information, see CAST and CONVERT (Transact-SQL).

When a column or expression of a user-defined type is returned to an SQL client as a result of a SELECT or FETCH statement, the way the client API accesses the column depends on whether the client API is a managed API (ADO.NET), or an unmanaged API (ODBC or OLE DB). If the client uses ADO.NET, the client can retrieve an instance of the type as a binary value by using the GetValue method on the SqlDataReader class, or as an object. If the client uses OLE DB, the client will receive user-defined type values as a binary value. If the client uses ODBC, the client will receive the user-defined type value as a binary value only.

You can perform ORDER BY, GROUP BY, and PARTITION BY operations with user-defined types if the type supports binary ordering. A type is binary ordered if it has the IsByteOrdered flag set to true in the SqlUserDefinedType attribute that is specified as part of the type definition. This flag indicates that the binary representation for the type is in the semantically correct order for that type.

You can create indexes on user-defined type columns if the type supports binary ordering. You can also create indexes on computed columns that are defined as method invocations off a user-defined type column, as long as the methods are marked deterministic. For more information, see CLR User-Defined Types.

Concepts

Other Resources

Help and Information