70-451–MCITP–Designing Program Objects–Designing CLR Stored Procedures, Aggregates and Types


Design CLR stored procedures, aggregates, and types.
This objective may include but is not limited to: assembly PERMISSION_SET, CLR vs. T-SQL, ordered vs. non-ordered, executing static methods on user-defined types, multi-parameter aggregations

My Take

For this bullet point it seems like we should be looking at the costs and benefits of using SQL CLR vs T-SQL for the Stored Procedures, User Defined Aggregates and User Defined Types. It appears that they would like for us to once again go over the PERMISSION_SET assembly option when creating an assembly which we looked at in the last section. We will also want to look at the ordered vs unordered settings again for these objects. Next we will look at the options for executing methods on user defined types and the options for multi-parameter aggregations.

It is probably advisable to watch the SQLCLR MCM Training Video . If you need even more info on CLR Types (and almost everything else relating to T-SQL goodness) I’d recommend getting the Inside Microsoft SQL Server 2008: T-SQL Querying And Inside Microsoft SQL Server 2008: T-SQL Programming books by Itzik Ben-Gan, et al.


I believe that this is the same discussed in the CLR TVF and Scalar UDF post. If so, just remember SAFE, EXTERNAL_ACCESS and UNSAFE.

Something that I failed to mention in the last post is that to set the PERMISSION_SET to EXTERNAL_ACCESS or UNSAFE the database will have to set the TRUSTWORTHY property to ON.


User Defined Types

User Defined Types (UDT) are used to extend the normal type system in sql server and can only be written in SQL CLR. They allow for storage of CLR types in a database and grant the ability to store multiple data elements and expose various methods to work on and with the data. The normal SQL Server data types do not grant this extra functionality (although some of the out of the box types in SQL Server 2008 are UDTs, such as Hierarchy ID and the GeoSpatial types).

CLR UDT columns can be added to a table, used as a variable, used in a stored procedures, and in functions as you would use a normal column. However, CLR objects need to be created in each database that you would like to use them in, including tempdb.

See User-Defined Type Requirements, Coding User-Defined Types, and Manipulating UDT Data BOL pages for more info.

User Defined Aggregates

User Defined Aggregates allow the aggregation of user defined types or custom aggregation in sql server and can also only be written in SQL CLR. For instance, you can sum an attribute of a UDT, or concatenate a string with input values.

User Defined Aggregates require the registered function to implement the required aggregation contract consisting of the SqlUserDefinedAggregate attribute and the aggregation contract methods (Init, Accumulate, Merge and Terminate).

See Requirements for CLR User-Defined Aggregates, Invoking CLR User-Defined Aggregates and Create Aggregate BOL pages for more info.

Stored Procedures

Stored Procedures can be coded in both T-SQL and CLR. The CLR stored procedures themselves can return data in multiple ways, including output parameters, tabular results and messages. When setting the assembly into UNSAFE or EXTERNAL_ACCESS the code can access items that might otherwise be unavailable to SQL Server (barring extended stored procedures, etc).

There are many trade-offs for using CLR in place of T-SQL for stored procedures including the following (These are true for functions as well):

  • You will have to measure to see if the procedural code benefits outweigh the data access penalties.
      • CLR is usually approximately 2 – 3 times slower for data access than T-SQL.
      • Procedural code, i.e. “Data Logic”, is usually faster in CLR.
  • CLR Data uses SqlCommand – which uses a string and is thus dynamic. You should use parameterized queries when accessing data through SQL Command.
  • CLR Data Access is not subject to dependency tracking
  • CLR always breaks ownership chains
  • In CLR Stored Procedures, you cannot wrap SQL Statements without direct table access.
    • Unless using EXECUTE as OWNER or code signing
  • SQLCLR code uses database resources.
    • The possibility of the CLR code being moved to the client should be considered if client is local.
    • If there is lots of data is to be retrieved the CLR code should reside on the server.
  • SQLCLR creates competition between RDBMS and computation… more to keep track of for a DBA.

See Choosing between CLR and T-SQL stored procedures: a simple benchmark

Ordered vs Non-Ordered

Trying to find more out about this, but I don’t see the ORDER(<order clause>) option for SQLCLR user defined types, aggregates or Stored Procedures that is present for the user defined functions.  Also, the few places that I have read about the new feature usually prefaced it with UDF…

Executing Static Methods on User-Defined Types

Static Methods need to be called with the type itself as opposed to the instance. For Example HIERARCHYID::GetRoot() instead of Select myHierarchyIDColumn.ToString() From myTable;

Multi-Parameter Aggregations

New for SQL Server 2008, you can now pass in multiple parameters to a user defined aggregate. Don’t know that there is much more to this section than that…

SQLCLR in SQL Server 2008: Multi-Input User-Defined Aggregates (Bob Beauchemin)

This entry was posted in SQL and tagged , , , . Bookmark the permalink.

One Response to 70-451–MCITP–Designing Program Objects–Designing CLR Stored Procedures, Aggregates and Types

  1. Pingback: MCITP 70-451 Links Page | Destination: Change

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s