Chapter 15 DBMS-Specific Features

CLR user-defined types

The introduction of user-defined types (UDTs) in SQL Server 2005 allows you to extend the scalar type system of the server, enabling storage of CLR objects in a SQL Server database. UDTs can contain multiple elements and can have behaviors, differentiating them from the traditional alias data types which consist of a single SQL Server system data type.

Because UDTs are accessed by the system as a whole, their use for complex data types may negatively impact performance. Complex data is generally best modeled using traditional rows and tables. UDTs in SQL Server 2005 are well suited to the following:

• Date, time, currency, and extended numeric types
• Geospatial applications
• Encoded or encrypted data

PowerDesigner models user-defined types as abstract data types.

Creating a user-defined type

To create a user-defined type, you must have an OOM open in the workspace, in order to specify the supertype.

To create a user-defined type:

1. Create an abstract data type by:
   
   
   • Select Model→Abstract data types to access the List of abstract data types, and click the Add a Row tool
   • Right-click the model or package in the Browser, and select New→Abstract Data Type
2. On the General Tab of its property sheet, select CLR from the list of Types.
3. Click the Select Class tool to the right of the Class field, in order to specify a supertype.
4. Click the Microsoft tab and select an assembly from the list to bind to the type.
5. Click OK to close the property sheet.

User-defined type properties

You can modify an object's properties from its property sheet. To open a user-defined type property sheet, double-click its diagram symbol or its Browser entry in the Abstract Data Types folder.

In addition to the standard abstract data type properties, a user-defined type has the following additional properties available on the Microsoft tab:

Copyright (C) 2006. Sybase Inc. All rights reserved.