SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Simple SQL CLR Integration


Simple SQL CLR Integration

Author
Message
Amit Anajwala
Amit Anajwala
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 23
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/aAnajwala/simplesqlclrintegration.asp
dougjjj
dougjjj
SSC Eights!
SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)

Group: General Forum Members
Points: 963 Visits: 648

I still haven't had the chance to use SQL2005 yet but this seems like quite a slow and painful way to do something that could be done so much easier in T-SQL, any reason on why you would go to all this trouble? Until you get down to the level of calling COM objects from your T-SQL I can't see any benefit from using C# or .Net to write SPs.

Anyone agree / disagree?





Mike Metcalf
Mike  Metcalf
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3139 Visits: 53

I'm sure that CLR will be usefull for situations where TSQL isn't quite up to the job.

Speaking personally, if a developer (with a lot of assistance) can't write something in TSQL and can prove that there's a proper reason for using CLR then i might be open to it, otherwise I just don't want to introduce it onto our systems.

Not everyone is good at writting optimised code (I'm being nice ) and allowing them to create their own stuff is pretty much saying 'here you go, add a bottleneck that we wont be able to find later'.


Mike C
Mike C
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9685 Visits: 1172
String-manipulation intensive operations are a prime example where the CLR will most likely kick T-SQL's rear end. I'm currently in the process of running some tests on the SQL 2K5 Express June CTP that perform millions of string manipulations in T-SQL as well as in a CLR UDF. So far T-SQL string manipulations can't even compare to a .NET StringBuilder in a UDF.

Another thing is bit-level manipulations. Ever try to encrypt or encode a string of bits using T-SQL? If you actually succeed, it will bring your SQL Server to a crawl. The CLR can handle extensive bit level manipulations much better than T-SQL.

Finally, there is a lot of O/S-level functionality you might want to call from within a T-SQL script. You might want to call the Windows Crypto API, or call the O/S and ask how much free disk space you have. COM is overkill for these type tasks.

That's the problem with oversimplified examples, like "Hello World". If all you're doing is returning the string "Hello World" you'd be better off with T-SQL. If you're trying to do something that requires a lot of string manipulation, excessive looping, Windows API calls, extensive bit-level manipulations, etc., you'd do well to look at UDF's.
Mike C
Mike C
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9685 Visits: 1172
Take a look at this article on Double Metaphone in T-SQL:

http://www.windowsitpro.com/Article/ArticleID/26094/26094.html?Ad=1

The author provides a T-SQL version of the source code. In my preliminary tests, my port of Double Metaphone to the SQL 2K5 CLR runs over 8 times faster than this T-SQL Code.

For me, getting 8 hours worth of T-SQL processing done in 1 hour is a pretty good and "proper reason for using CLR."
Adam Machanic
Adam Machanic
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4223 Visits: 733
StringBuilder is a great example. So is pretty much any math operation. Recently, I've been playing with the following method, which mathematically reverses an integer:

-----------------------------------------------------------
[SqlFunction]
public static int ReverseNumber (int number)
{
int reverse = 0;

do
{
reverse = (reverse * 10) + (number % 10);
number = number / 10;
}
while (number > 0);

return (reverse);
}
-----------------------------------------------------------



Here is the equivalent in a T-SQL UDF:



-----------------------------------------------------------
CREATE FUNCTION dbo.NumberReverse
(
@InputNum INT
)
RETURNS INT
AS
BEGIN
DECLARE @Reverse INT

SET @Reverse = @InputNum % 10
SET @InputNum = @InputNum / 10

WHILE @InputNum > 0
BEGIN
SET @Reverse = (@Reverse * 10) + (@InputNum % 10)
SET @InputNum = @InputNum / 10
END

RETURN (@Reverse)
END
GO
-----------------------------------------------------------


Run both of these 10000 or 100000 times on your system, using a script such as the following:

DECLARE @i INT
SET @i = 1
DECLARE @j INT
SET @j = 1234567
WHILE @i < 10000
BEGIN
SET @j = dbo.NumberReverse(@j)
SET @i = @i + 1
END


... On my test system, the CLR version runs twice as fast. I'd be interested in hearing others' results.

--
Adam Machanic
whoisactive
dougjjj
dougjjj
SSC Eights!
SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)

Group: General Forum Members
Points: 963 Visits: 648

This is the sort of thing that would be useful to know about SQL2005, after reading the previous article as a DBA you think ok great I can write code in CLR, a simple PRINT and a SELECT now takes 10 times as much code and is far more complex whats the big deal.

Possibly an article letting us know the benefits of using CLR over T-SQL might be worthwhile, but again depending on the type of work you're doing on your system excluding hardcore string manipulation and calling COM objects are there any other benefits to using the CLR over T-SQL?





Adam Machanic
Adam Machanic
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4223 Visits: 733
COM objects? Please, don't do that if you can avoid it. COM is unmanaged and unsafe.

Being able to call into .NET assemblies (and the .NET BCL), on the other hand, is one of the main benefits.

--
Adam Machanic
whoisactive
dougjjj
dougjjj
SSC Eights!
SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)SSC Eights! (963 reputation)

Group: General Forum Members
Points: 963 Visits: 648
Thats fair enough, I wouldn't use COM objects very often normally anyway, usually just FSO for pumping out the odd file here and there. So anyway on a general day to day basis how often would you use CLR compared to T-SQL? I can see the definatly see certain circumstances when the CLR would prove very useful but probably only accounting for 5% of the day to day work for most DBAs. I'm not against using it I just haven't had the chance to use it and am wondering how useful it really is.



Jeff Brumley
Jeff Brumley
SSC Veteran
SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)SSC Veteran (252 reputation)

Group: General Forum Members
Points: 252 Visits: 1

Curious about the CREATE ASSEMBLY command - does it just create a pointer, or does it actually compile the code into the SQL server? I'm trying to imagine the scenario of when you need to edit the CLR method - do you then need to drop the current assembly, and then rebuild the assembly and internal functions etc? Seems like a pain. If the CREATE ASSEMBLY just pointed to the dll then it seems like it would be easy.





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search