CLR
-
Hi,
As a DBA, how we use CLR?
Thanks
-
As a DBA, you shouldn't... there's not much that can't be done in T-SQL with the possible exception of RegEx (Regular expressions) and, despite what Books Online says about the performance of CLR's compared to T-SQL UDF's and the like, several of us have done some pretty extensive performance testing... except for RegEx, the T-SQL usually won the race.
If you insist on using CLR's and maintaining separate source code and documentation for them, see "CLR" in Books Online for how to use them.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Thanks Jeff
-
I was the other side of the testing with Jeff - so I'd largely agree with his assessment. I might throw in a few other categories (like - doing thing in the OS environment like file manipulation etc...) but by far and large - "data access is best done through T-SQL" (which by the way is a direct quote from the BOL "CLR best practices").
I will however take issue with the last piece (having to keep separate code bases). That was true up until fairly recently, but MS has now snuck in some new things for those ofrtunate to have access to Visual Studio Team Suite or the so called " Data Dude" (Visual Studio Team Edition for Database Professionals). I just saw a demo the other day and am still getting my head around what it can do and how it does it - but let's just say that it allows for a new database project allowing for the ENTIRE schema of a SQL 2005 database to be refactored in Visual Studio (kind of like the old DTS "copy objects" script, but hyped up on crack). Also has test script objects, existend and validation tests, etc... in short - the kitchen sink seems to be there.
There's a new interface (which got released in December), which also allowed for "upsizing" your regular team suite to have all of these goodies.
Pretty wicked stuff.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
This probably isn't "as a DBA" but CLR is also useful for intensive computations such as calculating a hash of a file, if you don't want to put it in the application layer. For example I use a CLR procedure for calculating and comparing salted hashes for passwords so that any application on any system can just call the extended stored procedure without having to reimplement the hashing algorithm. This means that an ASP web site, an ASP.NET website, a php website, a C application, a C# application and a Java application (and even a user running single queries) can all share the same code for calculating and comparing the hashes and implementing it in the application is very quick and easy with a guarantee that the different systems will never generate different hashes. T-SQL is great for manipulating data but writing a hashing algorithm in T-SQL would be a nightmare.
-
T-SQL is great for manipulating data but writing a hashing algorithm in T-SQL would be a nightmare.
Never tried it because I just haven't had to do it. However, with the use of tools like Tally tables and Row_Number, I don't understand why it would be any more of a nightmare in a language like T-SQL than it would be in any other language. Maybe I'm just missing something.
Only thing I've seen a need for a CLR for (especially performance wise), so far, is RegEx.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
One thing we use CLR code for is sending out alerts via SMS using a web service.
--------------------
Colt 45 - the original point and click interface
-
I have used CLR to import XML data that comes back from a web post
I am wondering now if there is a better alternative way to do this in sql server without CLR. I couldnt think of a way
the tool is a proper noun extractor written in Java and takes a string and breaks it down into proper nouns:
/* ?xml version="1.0" encoding="UTF8" ?>
root>
result>
nonpropernouns>
term ref="261420">
token end="7" node="0" start="4" type="word">off/token>
/term>
term ref="50127">
token end="21" node="0" start="15" type="word">Prince/token>
/term>
term ref="53754">
token end="30" node="0" start="25" type="word">Wales/token>
/term>
term ref="428780">
token end="38" node="0" start="31" type="word">tonight/token>
/term>
term ref="275015">
token end="45" node="0" start="42" type="word">see/token>
/term>
/nonpropernouns>
propernouns>
term>
token end="21" node="0" start="15" type="word">Prince/token>
token end="24" node="0" start="22" type="stopword">of/token>
token end="30" node="0" start="25" type="word">Wales/token>
/term>
/propernouns>
/result>
/root>People seem to be a bit anti CLR so could someone advise what would be a better way to go about it
-
The "anti CLR" thing is usually just that many people abuse CLR and use it when they shouldn't. For manipulating data, writing a normal stored procedure in T-SQL is better.
You should probably put your logic in the application layer though. It makes sense to just put the logic in the form handler for the web post.
-
CLR is good for things that T-SQL can't do. Accessing web services, file system. For normal data manipulation - T-SQL all the way.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Jeff Moden (1/21/2008)
T-SQL is great for manipulating data but writing a hashing algorithm in T-SQL would be a nightmare.
Never tried it because I just haven't had to do it. However, with the use of tools like Tally tables and Row_Number, I don't understand why it would be any more of a nightmare in a language like T-SQL than it would be in any other language. Maybe I'm just missing something.
Only thing I've seen a need for a CLR for (especially performance wise), so far, is RegEx.
I'm thinking he was talking about making something to return hash codes (think MD-5 hash of a document or a record). Interestingly enough - there's a built-in function that does just that (CHECKSUM), but rolling your own in any language (and I mean - doing it from scratch, and not just customizing an existing hash algorithm) is tricky at best in just about any language, mostly because the algorithm is so darned complicated. But that's an algorithmic complication, and not the language's fault.
That said - depending on what it was for - I might use a CLR solution OR a T-sql solution for that particular item (CLR if I was hashing files/documents, T-SQL if I was hashing records).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
Yes, this is the kind of hash I was talking about. A lot of the time they are not too complicated but rely on arrays of polynomials, operators such as bitwise rotations and other bitwise opertors which are probably not available in T-SQL (if they are, I have never seen them) and unsigned integers. An example of where I have used it is an implementation of MD5 modified to use some custom salting. Though this would be possible in T-SQL, it would probably take hundreds of lines and is not worth the effort. The CLR approach does not manipulate any data other than the small amount of data it is given so does not have to look anything up in the rest of the database, etc. Without salting hashes or modifying the algorithms at all, a hash can be calculated in CLR with something similar to
outparam = SHA512.Create().ComputeHash(inparam);(just using SHA512 as an example) -
True - but the "ease of coding" in CLR vs T-SQL in that case comes from having access to custom libraries (the encryption/decription namespaces, etc..) in CLR, which aren't exposed in T-SQL by design. Not exactly a limitation of the language itself, expecially for functions you wouldn't necessarily use a database for. I mean - it doesn't have built-in functions for drawing Circles on-screen either, but I can hardly fault T-SQL for that, now can I?
That's more of a case of using the right tool for the right purpose.
The bitwise operators ARE in fact available in Transact-SQL.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
If you think that it is only easier in CLR because of the .NET API, write the CRC32 algorithm in T-SQL and in C# 2.0+ then compare size and calculation speed on a blob or something similar.
The right tool for the job is exactly the point. Both the syntax and API make CLR more appropriate for this particular job. T-SQL is more appropriate for other jobs.
Which bitwise operators are in T-SQL because as far as I was aware it is just &, |, ~ and ^.
-
Well - if you want to me acknowledge that writing procedural code is easier to do in a procedural language than in a set processor...Well - sure...:)
And - what other bitwise operators do you need? You've got all of the ones you need...:) They actually threw in one "extra" one, since you technically don't need XOR to do all binary operations.....
You mean - you CAN't disable the bomb with a pen-knife and a bubblegum wrapper????
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply