I thought it time to recap some old database theory ahead of SQL Sevrer 2005 and the potential pain that CLR integration may bring to the DBA/Developer relationship.

Like all technology, if it's not used correctly it's just plain dumb.  CLR integration has been discussed between gurus whose shoes I am noth worth to clean ad nauseum, so we won't cover it here.  In a nutshell we are talking here about the integration of the Microsoft .NET Framework and runtime into Microsoft SQL Server.  This will allow you to write managed code in the database and a bunch of other cool things.

The thing to be mindful I think is when dealing with a .NET complex type.  Consider you're dealing with point data (i.e. x,y,z coordinates in space).  How would you represent this in relational data?  It may be convienient to store each point as some kind of string (NCHAR(5) in SQL Server types maybe) but that's just poor normalization.  What then happens if you want to process all the y values?

More correct may be three integer or float columns - one each for x, y and z.  This is getting there but what could be a scalar result from this?  It would have to be a concatination like:

SELECT cast([x] AS VARCHAR(3)) + ',' + cast([y] AS VARCHAR(3)) + ',' + cast([z] AS VARCHAR(3)) AS Point

to get

Point      
-----------
4,20,9

(1 row(s) affected)

...And that's ugly :)

So what's the bottom line here?  One of the most exciting things I am looking forward to in SQL 2005 CLR code is the idea of using a managed custom class to describe a point, and having it as a scalar result in a T-SQL operation.  That's hot if you ask me!

So what could be the problem?  Some peanut somewhere is probabbly going to use the new found CLR powers and code up a Tetris implementation and have it loaded into the CLR inside SQL 2005.  Sure you could do it, but all you are going to do is anger some DBA and give a bad name to CLR code, making it harder to adopt the correct implementations out in the field.

I'd love to give some credit where it is due for this post, but I can't remember whose writing it was that got me started thinking about this.  Whoever you are, thanks!