I have a C# project to implement a complex number type and wanted to execute
SELECT DISTINCT c1 FROM complex
This gives the error:
Msg 421, Level 16, State 1, Line 1
The ComplexNumber data type cannot be selected as DISTINCT because it is not comparable.
I then implemented the IComparable interface for the C# struct, but this made no difference.
Is there a way to make a CLR implemented data type recognized as 'comparable' to SQL?
I've already found that there doesn't seem to be a way to overload operators in SQL so I have to do
declare @c ComplexNumber
declare @d ComplexNumber
set @c = '1+0i'
set @d = @c
// Result is 2+0i
So I won't be surprised if the answer is a flat "No".
I also found that
SELECT c1 FROM complex GROUP BY c1
Msg 249, Level 16, State 1, Line 2
The type "ComplexNumber" is not comparable. It cannot be used in the GROUP BY clause.
So, as an alternative, is there some way to do the equivalent of 'SELECT DISTINCT' or 'SELECT ... GROUP BY' in a set based way when using CLR defined types?
By the way, I'm actually using SQLserver 2012 (not 2005) but decided this was the most appropriate forum.