Click to Rate and Give Feedback
MSDN
MSDN Library
SQL Server
SQL Server 2008
Database Engine
Development
 Performing Operations on User-defin...
Community Content
In this section
Statistics Annotations (0)
Other versions are also available for the following:
SQL Server 2008 Books Online (October 2008)
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.

ms187630.Caution(en-us,SQL.100).gifCaution:
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.

ms187630.Caution(en-us,SQL.100).gifCaution:
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

ms187630.note(en-us,SQL.100).gifNote:
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.

Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
Processing
© 2008 Microsoft Corporation. All rights reserved. Terms of Use  |  Trademarks  |  Privacy Statement
Page view tracker