Is it possible to make an SQLCLR data type 'comparable'?

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » CLR Integration and Programming. » Is it possible to make an SQLCLR data type...

Is it possible to make an SQLCLR data type 'comparable'?

Rate Topic

Display Mode

Topic Options

Author

Message

Derek Dongray

Posted Wednesday, February 13, 2013 6:44 PM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342, Visits: 1,946

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

Derek Dongray

Posted Thursday, February 14, 2013 1:02 PM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342, Visits: 1,946

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

opc.three

Posted Tuesday, February 19, 2013 7:17 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:07 PM
Points: 6,826, Visits: 11,951

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1421637

« Prev Topic | Next Topic »

Permissions