Running managed code in SQL Server 2008 - any issues?

Question

I would like to know the performance issues associated with running managed code through SQL Server 2008. I've heard about some memory and speed issues.

Specifically, I want to implement a SHA256 hashing DLL and execute it as a sproc with SQL Server 2008.

Alternately, I could simply execute the hashing from my .Net app, then pass the string to my sprocs.

Pros/cons?

Thanks.

Answer 1

SQLCLR is quite fast. Use it if you have a clear need to execute CLR code within T-SQL. For example, say you write a SQLCLR function with the signature:

SqlString Hash(SqlString input)

and you manage to get it all running the way it should. You could execute the following query:

IF EXISTS(SELECT * FROM Users WHERE HashedPassword = dbo.Hash(@userPassword) AND UserName = @userName)
BEGIN
    SELECT 'You''re alright, Jack.';
END
ELSE
BEGIN
    SELECT 'Bogus.';
END

If you can manage to hash the hypothetical password field in your app, you are better off to do so within the app and pass the hashed value into SQL Server to run a query like this:

SELECT *
FROM User
WHERE UserName = @userName AND HashedPassword = @hashedPassword

This is better for a few reasons:

1. You aren't putting business logic into SQL Server, where it will be hard to test.
2. The query itself is much less complicated, so SQL Server will be doing less work, and therefore be faster.
3. Setting SQLCLR up can be a pain (SQLCLR is off by default). Deploying your assemblies into SQL Server can be a pain, especially if you don't have direct access to the production server.
4. If you don't use SQLCLR, when you update/deploy your app, you won't have to remember to update/deploy the CLR stuff in SQL Server. This reduces your maintenance efforts.