Performing Operations on User-defined Types
You can perform a variety of operations on instances of user-defined types. These operations include the following:
Invoking methods or retrieving properties of a user-defined type.
Performing data type conversions.
Returning user-defined type values to an SQL client.
Using ORDER BY clauses.
Creating indexes on user-defined type columns.
Creating computed columns.
Invoking Methods or Retrieving Properties of User-defined Types
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.
Warning
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.
Warning
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
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.
Performing Data Type Conversions with User-defined Types
You can perform implicit and explicit conversion with user-defined types as summarized in the following table.
|
TO: User-Defined Type |
Binary |
nvarchar |
xml |
|---|---|---|---|---|
FROM: User-Defined Type |
|
Explicit |
Explicit |
Implicit |
Binary |
Implicit |
|
|
|
nvarchar |
Implicit |
|
|
|
xml |
Implicit |
|
|
|
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).
Returning User-defined Type Values to an SQL Client
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.
Using ORDER BY, GROUP BY, and PARTITION BY Clauses with User-defined Types
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.
Creating Indexes on User-defined Type Columns
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.