Using SQL CLR to run a Client Side query in Server Side

SQL 2005 CLR only supports .Net 2.0 framwork by default. I have imported the .Net 3.5 framework into SQL server before, but it is somewhat messy and opens up some security holes (don't recall the all the details), but the quick and dirty is in this question -- in particular

CREATE ASSEMBLY [System.Core]
AUTHORIZATION [dbo]
FROM 
'C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.5\System.Core.dll'
WITH PERMISSION_SET = UNSAFE

The other roadblock that you typically encounter in CLR procs is the security context from the user running the stored proc is not inherited by the CLR proc, so trying to accomplish some things can be harder than you would expect.

Debugging a CLR proc is not as easy. You can do it, and I was never able to get a full stack backtrace (with line number, etc.). So, debug before you move it to CLR as much as possible.

Other than that, I have not had much trouble with CLR procs -- they work pretty well as long as you have a handle on possible performance issues by running complex code inside SQL, don't allocate lots of memory inside your CLR proc -- you will regret it.

ADDED

One other issue I thought of. CLR proc code writing needs a higher level of proficiency than typical client side code or stored procs. This is a maintenance consideration.

Updates to the CLR proc are also more complicated the client side code or sql procs, so this can also be a problem. This also complicates maintenance.

I'm not trying to discourage CLR procs, the upsides are also good -- performance, flexibility, security (have to have DB permissions to update). But, I was trying to documents the issues that they don't tell you about when you read about "how great and simple CLR procs are".

ADDED

You don't give detail, but if you are data bound (lots of rows) and you can write the logic as set-based TSQL performance is almost certain to be much better as set-based. TSQL is slow if you try to do lots of computation via TSQL -- scripting runs slow, database I/O runs fast. TSQL is not very flexible as a programming language, so CLR adds flexibility and faster code execution.

If you are not familiar with using APPLY in a select statement (Sql 2005+), you should take the time to understand as it can be very useful in keeping complex query "set based" -- you don't ever want to use a cursor if you can avoid it -- slow and chews up database resources.

You might save yourself some drag on sending the results across the wire, though if you are on a 1 GB network then it might not matter, especially since it is fairly vague as to what "tons of rows" means. Are we talking in the hundreds of thousands, or millions?

In either case, I am not sure that there is a clear understanding here as to what SQL CLR does based on the statement "use SQL CLR to implement the whole query including the Comprarer class in that way the whole query run in server side". You cannot write a query in SQL CLR. Creating .Net / CLR Stored Procedures and Functions does not replace T-SQL for interaction with the database. This means that you are still going to need to execute a SELECT statement and get the results back. Using LINQ to SQL within a SQL CLR object will still execute the same SQL as it does right now from the client.

If more details are provided as to the end goal of doing the comparison then it might be possible to plan a more appropriate solution. But given the question as asked, it seems doubtful that moving this code into SQL Server, assuming the comparison is still done in the .Net code, will provide much, if any, benefit.

EDIT: To be clearer: transferring the business logic to run server-side in such a way as to avoid pulling in all rows into memory such that they can be compared via your custom comparer would require that you create a SQL CLR function and use it in a WHERE clause. So the model would be changed to essentially send one row at a time to the function for comparison rather than have all available in a collection.