THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

What types of objects are useful in SQL CLR?

This blog has moved! You can find this content at the following new location:

http://greglow.com/index.php/2011/02/26/what-types-of-objects-are-useful-in-sql-clr/

Published Friday, February 25, 2011 3:32 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Gorm Braarvig said:

Great article!

I generally agree with you observation. Some personal comments:

- Scalar UDF is suboptimal (and I still dream of better unmanaged support), performance is not very good, and when you add that the cost of deployment and app-lifecycle is high, I end up with this being a poor choice in most real-world scenarios I have stumbled uppon. For example, the MD5 checksums have much better performance in TSQL, even if you need to put in additional logic with TSQL to solve HASHBYTES 8K limit, NULL-replacements and those kind of things.

If/when .NET4 gets supported, there might be some more cases for UDF, eg using memory mapped files, LINQ, workflow or other.

- The CLR aggregates are great and can be used to create advanced functionality where lot's of data needs to be taken into account to create simple results (say for Identity resolution scenarios). The 2G limit is of couse not the real limit, since you have much less memory available inside the CLR in the real world, and scenarios where this is a factor should obviously be solved outside.

/G

February 25, 2011 4:44 AM
 

Abdalla said:

Very true, I used Scalar UDF when I had to calculate the check-digit and verify the ISBN check-digit.

February 26, 2011 6:05 PM
 

Chris Randall said:

Great post, Greg. My students will definitely benefit from it.

February 28, 2011 10:40 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.

Archives

Privacy Statement