SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Getting The Most Out of SQL Server 2005 UDTs and UDAs


Getting The Most Out of SQL Server 2005 UDTs and UDAs

Author
Message
Solomon Rutzky
Solomon Rutzky
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4829 Visits: 3066
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/srutzky/3208.asp

SQL# - https://SQLsharp.com/
Sql Quantum Lift - http://SqlQuantumLift.com/
Adam Machanic
Adam Machanic
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4223 Visits: 733
"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
Indraneel Phadke
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 23
Hi Adam...

Hope you post this soon!

Regards,
NeelSmile
Sam Stange
Sam Stange
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 67
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
Solomon Rutzky
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4829 Visits: 3066
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/
Sql Quantum Lift - http://SqlQuantumLift.com/
Sam Stange
Sam Stange
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 67
I agree. Thanks again for your post, implementing this aggregate function will save myself more work than you could possibly know.
Solomon Rutzky
Solomon Rutzky
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4829 Visits: 3066
Glad you found it helpful. :-)

SQL# - https://SQLsharp.com/
Sql Quantum Lift - http://SqlQuantumLift.com/
massoud-1045248
massoud-1045248
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 46
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
Solomon Rutzky
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4829 Visits: 3066
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/
Sql Quantum Lift - http://SqlQuantumLift.com/
massoud-1045248
massoud-1045248
SSC-Enthusiastic
SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)SSC-Enthusiastic (147 reputation)

Group: General Forum Members
Points: 147 Visits: 46
Very much appreciated!
This article has been very useful!
Thanks again
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search