Export (0) Print
Expand All
0 out of 1 rated this helpful - Rate this topic

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.

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 noteCaution

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 noteCaution

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

NoteNote

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.

 

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).

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.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.