SQL CLR User Defined Types and Operator Overloading

  • Article

Just talked with Shaun Hayward. Someone who onviously very passionate about coding. A (long) while ago he asked me for my opinion on Operator Overloading for User Defined types in SQL Server 2005.

Shaun

      Good day

I came across your blog while looking for Yukon UDT info.
I've found that I can't deploy an assembly if any of my UDT's have overloaded the = operator.
I think this is strange since the SQL code would make a lot of sense:

SELECT MyTable.PlaceName FROM MyTable
WHERE MyTable.GPSCoordinateField='49.25,17.36'

Just wondering if you have any thoughts.
- Shaun

Me

I just found this email underneath whole lot of others. I’m really sorry for the really late reply.

What you suggest would definitely make the code more esthetic. I’m not sure whether there are any other benefits since all can be achieved by writing a method on the UDT or by actually instantiating a second object and comparing them. In any case I can understand why the product chose not to invest in Operator Overloading.

Also, Operator Overloading does seem to be more challenging in an environment where you would use .NET code to overload a SQL operator. The ‘=’ in this statement is still a SQL Operator, agree?

Shaun :-)

Hi Hans

Thanks for the reply.

We could get into a big philosophical debate about whether or not the “=” is really a SQL operator. I mean, what is an operator but a shortcut for calling a function? I could be wrong, but my guess is that .NET compiles = in VB.NET to one of those funky “op_” methods. Most operators seem to do this.

After all, everything (property, method, constructor, operator, or event) is just a method call in disguise. For that matter, .NET in general should be able to support custom operator defining. But I digress…

The problem is that SQL Server has certain expectations for its operators. How can you ensure that hack developers actually do the right thing with an operator? What if they toss code into an endless loop? What if they call unmanaged code and throw an IPF or GPF?

So I fully appreciate why SQL Server cannot support them at this time. I suspect, however, that future incarnations will come up with ways to handle all of these problems and an operator will be neither a .NET operator nor a SQL Operator. .NET is blurring these lines, much to the praise of developers – myself included.

For now, there is a work around by my philosophy of idealism remains much the same :-)

Once we have full operator control in SQL Server next then my next task shall be to elect an honest politician!