CLR Assemblies (SQL Server)

An assembly is a DLL file used to deploy functions, stored procedures, triggers, user-defined aggregates, and user-defined types that are written in one of the managed code languages hosted by the Microsoft .NET Framework common language runtime (CLR), instead of in Transact-SQL. PowerDesigner models assemblies as extended objects with a stereotype of <<Assembly>>.

Creating an Assembly

You can create an assembly in any of the following ways:
  • Select Model > Assemblies to access the List of Assemblies, and click the Add a Row tool.

  • Right-click the model (or a package) in the Browser, and select New > Assembly.

Assembly Properties

You can modify an object's properties from its property sheet. To open an assembly property sheet, double-click its diagram symbol or its Browser entry in the Assemblies folder.

The following extended attributes are available on the Microsoft tab:

Name

Description

Authorization

Specifies the name of a user or role as the owner of the assembly.

Scripting name: Authorization

File name

Specifies the local path or network location where the assembly that is being uploaded is located, and also the manifest file name that corresponds to the assembly. Can be entered as a fixed string or an expression evaluating to a fixed string.

Scripting name: FileName

Permission set

Specifies a set of code access permissions that are granted to the assembly when it is accessed by SQL Server. You can choose between:

  • SAFE

  • UNSAFE

  • EXTERNAL_ACCESS

Scripting name: PermissionSet

Visibility

Specifies that the assembly is visible for creating common language runtime (CLR) functions, stored procedures, triggers, user-defined types, and user-defined aggregate functions against it. You can choose between:

  • On

  • Off

Scripting name: Visibility

Unchecked data

By default, ALTER ASSEMBLY fails if it must verify the consistency of individual table rows. This option allows postponing the checks until a later time by using DBCC CHECKTABLE.

Scripting name: UncheckedData