CLR User-Defined Types (SQL Server)

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.

Since UDTs are accessed by the system as a whole, their use for complex data types may negatively impact performance, and complex data is generally best modeled using traditional rows and tables. UDTs in SQL Server are well suited to date, time, currency, and extended numeric types, geospatial applications, and 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 already created an assembly, and have an OOM containing an appropriate class open in the workspace, in order to specify the supertype:
  1. Select Model > Abstract Data Types to access the List of Abstract Data Types, and click the Add a Row tool (or 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 Object 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.

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:

Name

Description

Assembly

Specifies the assembly to bind with the abstract data type.

Scripting name: Assembly

Mandatory

Specifies whether the type can hold a null value.

Scripting name: Mandatory
Parent topic: Common Language Runtime (CLR) Integration (SQL Server)