Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is it possible to make an SQLCLR data type 'comparable'? Expand / Collapse
Author
Message
Posted Wednesday, February 13, 2013 6:44 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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

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 c1

gives
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
Post #1419790
Posted Thursday, February 14, 2013 1:02 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,344, Visits: 1,983
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
Post #1420264
Posted Tuesday, February 19, 2013 7:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:57 AM
Points: 7,080, Visits: 12,571
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
Post #1421637
Posted Monday, July 29, 2013 1:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 29, 2013 1:47 PM
Points: 1, Visits: 1
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.
Post #1478723
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse