Getting The Most Out of SQL Server 2005 UDTs and UDAs

Question

Getting The Most Out of SQL Server 2005 UDTs and UDAs

Solomon Rutzky

SSCoach

Points: 16430
More actions
August 23, 2007 at 10:02 am

#178235

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/srutzky/3208.asp
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Adam Machanic SSCoach Points: 15370 More actions · Answer 1

"Unfortunately, there is not much we can do at this point since the data has already been compressed as much as possible and no matter what we do we have that 8000 byte limit."

I hope you'll excuse my blatant self-promotion, but you can go much further than this for UDAs -- and I explain how to do it in the SQLCLR chapter of "Expert SQL Server 2005 Development" ... (which you can find here: http://www.amazon.com/dp/159059729X )

And while I'd appreciate it if anyone who is really serious about this topic buy the book, I won't make you spend your money just for this solution. So if you're interested (reply here if so), I will post a followup in a few days. In the meantime, I think this is a good puzzle, so try to solve it yourself first. Here is a hint:

Think about how to leverage the fact that you have a runtime environment at your disposal. Focus on garbage collection. By using this compression method, each uncompressed value will have to be garbage collected twice: once for the value passed in from SQL Server, and again for the value decompressed/deserialized from the compressed/serialized stream. Is it possible to set this up so that each value will be garbage collected only once?

--
Adam Machanic
whoisactive

Indraneel Phadke Grasshopper Points: 23 More actions · Answer 2

Indraneel Phadke

Grasshopper

Points: 23

January 7, 2008 at 12:22 pm

#765477

Hi Adam...

Hope you post this soon!

Regards,

Neel:)

Sam Stange SSC Enthusiast Points: 123 More actions · Answer 3

For those stuck at 8000 bytes setting for MaxByteSize, SQL Server 2008 has bumped this amount to 2 GB! Just set MaxByteSize = -1. Only challenge to this, is you have to deploy the CLR manually, but it's worth it.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 4

Sam Stange (6/29/2009)
For those stuck at 8000 bytes setting for MaxByteSize, SQL Server 2008 has bumped this amount to 2 GB! Just set MaxByteSize = -1.

Hi Sam. Yes, in SQL Server 2008 you can use -1 to go beyond the previous 8000 byte limit (and I have been meaning to update this article to include that info). However, I still think it might be best to consider also using the compression method in conjunction with the -1 option so that one does not use up too much memory since that is still a valuable resource.

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Sam Stange SSC Enthusiast Points: 123 More actions · Answer 5

I agree. Thanks again for your post, implementing this aggregate function will save myself more work than you could possibly know.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 6

Glad you found it helpful. 🙂

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

massoud-1045248 SSC-Addicted Points: 435 More actions · Answer 7

Thanks for the article but it seems that the link is not working.

CreateAssemblyAndAggregate.sql

Also when I try to create the procedure below I get an error. I named the dll MedianSQLSERVER

Error Message:

Msg 6505, Level 16, State 1, Procedure Agg_Median, Line 1

Could not find Type 'Agg_Median' in assembly 'MedianSQLServer'.

CREATE PROCEDURE Agg_Median

AS

EXTERNAL NAME [MedianSqlServer].[Agg_Median].[Accumulate]

--Assembly.type.

Thank you

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 8

massoud-1045248 (3/14/2011)
Thanks for the article but it seems that the link is not working.
CreateAssemblyAndAggregate.sql
Also when I try to create the procedure below I get an error. I named the dll MedianSQLSERVER
Error Message:
Msg 6505, Level 16, State 1, Procedure Agg_Median, Line 1
Could not find Type 'Agg_Median' in assembly 'MedianSQLServer'.
CREATE PROCEDURE Agg_Median
AS
EXTERNAL NAME [MedianSqlServer].[Agg_Median].[Accumulate]
--Assembly.type.

Hello. The link should be fixed now, thanks to the editor, Steve Jones.

Also, you are using a 3-part name to point to the Aggregate, pointing specifically to the Accumulate method. You should not point to the method within the Aggregate, just to the Aggregate itself via a 2-part name. You are also missing the input parameter and the return type. And you call CREATE AGGREGATE instead of CREATE PROCEDURE. I assume it would be something like:

CREATE AGGREGATE [dbo].[Agg_Median] (@value [float])

RETURNS [float]

EXTERNAL NAME [MedianSqlServer].[Agg_Median]

Take care,

Solomon...

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

massoud-1045248 SSC-Addicted Points: 435 More actions · Answer 9

Very much appreciated!

This article has been very useful!

Thanks again

massoud-1045248 SSC-Addicted Points: 435 More actions · Answer 10

Hi guys, I am using the CLR and for some reason I get this error :

A .NET Framework error occurred during execution of user-defined routine or aggregate "Agg_Median":

System.InvalidCastException: Specified cast is not valid.

System.InvalidCastException:

at Agg_Median.Write(BinaryWriter ToBeSerialized)

Any idea on how to fix this?

Thanks

massoud-1045248 SSC-Addicted Points: 435 More actions · Answer 11

Hi everyone, not sure if anyone is still looking at this thread but recently I have been running into the following error. I inspected the data but I am not noticing anything unusual.

Any input would be greatly appreciated.

Thanks

Msg 6522, Level 16, State 2, Line 2

A .NET Framework error occurred during execution of user-defined routine or aggregate "Agg_Median":

System.InvalidOperationException: Failed to compare two elements in the array. ---> System.ArgumentException: At least one object must implement IComparable.

System.ArgumentException:

at System.Collections.Comparer.Compare(Object a, Object b)

at System.Array.SorterObjectArray.SwapIfGreaterWithItems(Int32 a, Int32 b)

System.InvalidOperationException:

at System.Array.SorterObjectArray.SwapIfGreaterWithItems(Int32 a, Int32 b)

at System.Array.SorterObjectArray.QuickSort(Int32 left, Int32 right)

at System.Array.Sort(Array keys, Array items, Int32 index, Int32 length, IComparer comparer)

at System.Collections.ArrayList.Sort(Int32 index, Int32 count, IComparer comparer)

at System.Collections.ArrayList.Sort()

at Agg_Median.Write(BinaryWriter ToBeSerialized)

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 12

massoud-1045248 (1/29/2015)
Hi everyone, not sure if anyone is still looking at this thread but recently I have been running into the following error. I inspected the data but I am not noticing anything unusual.
Any input would be greatly appreciated.
Thanks
Msg 6522, Level 16, State 2, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "Agg_Median":
System.InvalidOperationException: Failed to compare two elements in the array. ---> System.ArgumentException: At least one object must implement IComparable.
System.ArgumentException:
at System.Collections.Comparer.Compare(Object a, Object b)
at System.Array.SorterObjectArray.SwapIfGreaterWithItems(Int32 a, Int32 b)
System.InvalidOperationException:
at System.Array.SorterObjectArray.SwapIfGreaterWithItems(Int32 a, Int32 b)
at System.Array.SorterObjectArray.QuickSort(Int32 left, Int32 right)
at System.Array.Sort(Array keys, Array items, Int32 index, Int32 length, IComparer comparer)
at System.Collections.ArrayList.Sort(Int32 index, Int32 count, IComparer comparer)
at System.Collections.ArrayList.Sort()
at Agg_Median.Write(BinaryWriter ToBeSerialized)

Hello Massoud. Are you using the code from the article exactly as it is shown or did you make any modifications? I have no idea how it could be possible to get the error you posted above, nor the error you mentioned in the prior post (sorry, I never got a notification for that posting). Do you always or only sometimes get this error? The only problem I see, looking over the code again, is that it doesn't handle groups that have only NULL values, but that causes a different error: "Index was out of range". What version of SQL Server are you using? Can you post some sample data? How did you get past the error from last time regarding "Specified cast is not valid"?

Take care,

Solomon..

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

massoud-1045248 SSC-Addicted Points: 435 More actions · Answer 13

Hello Solomon, thanks for your reply. It's been a while but I use this code to compute a median for a list that varies every day.

so basically date(T), median(list(T)).

meaning : 01/02/2015 Median (list), 01/03/2015 Median (list) ....

I think my work around was to exclude some dates and move on when it wasn't working. Now when I go and compute without the time series and look at a specific date , it works .

For example when I have the time series with the specific condition

"valuationdate > getdate() - 235" in the where statement which gives me data up to 06/09/2014 but I get the error message :

Msg 6522, Level 16, State 2, Line 1

A .NET Framework error occurred during execution of user-defined routine or aggregate "Agg_Median":

System.InvalidOperationException: Failed to compare two elements in the array. ---> System.ArgumentException: At least one object must implement IComparable.

System.ArgumentException:

at System.Collections.Comparer.Compare(Object a, Object b)

at System.Array.SorterObjectArray.SwapIfGreaterWithItems(Int32 a, Int32 b)

System.InvalidOperationException:

at System.Array.SorterObjectArray.SwapIfGreaterWithItems(Int32 a, Int32 b)

at System.Array.SorterObjectArray.QuickSort(Int32 left, Int32 right)

at System.Array.Sort(Array keys, Array items, Int32 index, Int32 length, IComparer comparer)

at System.Collections.ArrayList.Sort(Int32 index, Int32 count, IComparer comparer)

at System.Collections.ArrayList.Sort()

at Agg_Median.Write(BinaryWriter ToBeSerialized)

but when I exclude via inserting the following in the where statement

and valuationdate not in ('06/06/2014','06/07/2014','06/08/2014') it still doesn't work if I increase to

"valuationdate > getdate() - 236"

And when I look at 06/08/2014 specifically, it returns no error message.

Happy to share more data if needed.

Thanks again.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 14

massoud-1045248 (1/29/2015)
Hello Solomon, thanks for your reply. It's been a while but I use this code to compute a median for a list that varies every day.
so basically date(T), median(list(T)).
meaning : 01/02/2015 Median (list), 01/03/2015 Median (list) ....
I think my work around was to exclude some dates and move on when it wasn't working. Now when I go and compute without the time series and look at a specific date , it works .

Hi again. So, if I am understanding you correctly, you are doing a MEDIAN of a list of DATETIME (or just DATE) instead of FLOAT? I had never considered such a thing. Interesting. But I can see one immediate problem: what to do in the case of an even list, since DATE / DATETIME values cannot be averaged. I suppose you could just pick the higher or lower value in each case, or in SQL Server 2008 and newer, pass in a second parameter to determine which side to err on.

If you want to continue debugging this, since it really isn't the code from the article, please post a message to the CLR Integration and Programming forum and we can take it from there.

Take care,

Solomon..

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR