February 13, 2013 at 6:44 pm
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 dodeclare @C ComplexNumber
declare @d ComplexNumber
set @C = '1+0i'
set @d = @C
select @C.Plus(@d).ToString()
// 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 c1gives
Msg 249, Level 16, State 1, Line 2
The type "ComplexNumber" is not comparable. It cannot be used in the GROUP BY clause. As expected! 🙂
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.
Derek
February 14, 2013 at 1:02 pm
It seems the solution is to make sure the type is binary comparable and to set IsByteOrdered = true in the arguments to the Microsoft.SqlServer.Server.SqlUserDefinedType attribute.
Of course, the problem then is to ensure that the binary format is correctly ordered!
Derek
February 19, 2013 at 7:17 am
Derek Dongray (2/14/2013)
Of course, the problem then is to ensure that the binary format is correctly ordered!
Need any assistance on this piece or have you found what you needed?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 29, 2013 at 1:19 pm
Sorry for mining, but i run into the same problem. My udt must have own serialization and i don't how to implement (if it is possible) my own procedure for byte ordering. Thanks for help in advance.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply