2

SQL Server allows you to create CLR functions, stored procedures, user types and other objects, for purpose that are really complex to be done inside SQL.

But, can someone compare those two things: TSQL Object and CLR Object, in sense of performance, benefits, and so on.

What are real situations for usage CLR objects?

Is there any best practices proposition for their usage?

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
veljasije
  • 6,722
  • 12
  • 48
  • 79
  • Have you read http://stackoverflow.com/q/439859/21567? – Christian.K Apr 10 '13 at 09:35
  • Good article, but I don't see any words about performance. Is there any additional overhead in SQL Server if it works with CLR objects? – veljasije Apr 10 '13 at 09:41
  • 1
    The point about performance is, that it becomes a secondary concern, when you need something that is only doable with the CLR (like user types, user aggregates, etc.). Even if a job can be done either way - using T/SQL or CLR - I'm not sure that there is one-fits-all recommendation here. In the end you'd have to weigh _all_ pros and cons of either approach and of course measure actual performance. – Christian.K Apr 10 '13 at 09:45

2 Answers2

1

What are real situations for usage CLR objects?

SQL Server lacks an aggregate string concatenation function. This bizarre oversight leads to all manner of complicated work-arounds.

Creating and using a custom CLR aggregate function is a clean solution to this problem, and is in fact the reference example given in MSDN article on the subject of custom aggregate functions:

http://msdn.microsoft.com/en-us/library/ms131056.aspx

Performance

There's an MSDN article that gives at least a theoretical (no metrics) overview:

http://msdn.microsoft.com/en-us/library/ms131075.aspx

And here's a more practical post (with metrics) from AboutSqlServer.com:

http://aboutsqlserver.com/2013/07/22/clr-vs-t-sql-performance-considerations/

david.barkhuizen
  • 5,239
  • 4
  • 36
  • 38
0

There are two questions here that need to be addressed separately.

  1. In terms of functionality & benefits, I wrote an article (part of a series on the topic of SQLCLR) that looks at what uses of SQLCLR are "appropriate", mainly by looking at what it can do that cannot be done otherwise, or not done nearly as easily. That article is "Stairway to SQLCLR Level 1: What is SQLCLR?" (free registration required) and it is summarized in an answer to the question that was linked in a comment on the question, Advantage of SQL SERVER CLR.

  2. In terms of performance, I published a study a few years ago (July, 2011) that detailed various scenarios and tested the raw SQL, that SQL in a T-SQL function, and that algorithm in a CLR-based function. I tested both scalar functions and table-valued functions. That article is "CLR Performance Testing" (no registration required). Please keep in mind that the testing was done on SQL Server 2008 and there were performance improvements made in SQL Server 2012 regarding deterministic scalar CLR-based functions. Meaning, the performance results of the CLR functions would be better if re-running those tests on at least SQL Server 2012 if not a newer version. But the conclusion, even on SQL Server 2008 without having those improvements, is that it depends on many factors and sometimes CLR is faster and sometimes T-SQL is faster. But, most often a formula that can be expressed in simple to moderate T-SQL as part of the query, and not abstracted to a function of either type, is by far the fastest.

Community
  • 1
  • 1
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171