Getting The Most Out of SQL Server 2005 UDTs and UDAs

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Solomon Rutzky » Getting The Most Out of SQL Server 2005 UDTs...

Getting The Most Out of SQL Server 2005 UDTs and UDAs

Rate Topic

Display Mode

Topic Options

Author

Message

Solomon Rutzky

Posted Thursday, August 23, 2007 10:02 AM

SSC Veteran

Group: General Forum Members
Last Login: Saturday, May 18, 2013 11:12 AM
Points: 285, Visits: 1,377

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/srutzky/3208.asp

SQL# - http://www.SQLsharp.com/

Post #393364

Adam Machanic

Posted Wednesday, September 19, 2007 8:50 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 3:20 PM
Points: 1,137, Visits: 667

"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
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web

Post #400393

Indraneel Phadke

Posted Monday, January 07, 2008 12:22 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, April 21, 2011 12:37 AM
Points: 1, Visits: 23

Hi Adam...

Hope you post this soon!

Regards,
Neel:)

Post #439749

Sam Stange

Posted Monday, June 29, 2009 11:28 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, March 25, 2011 9:34 AM
Points: 17, 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.

Post #743837

Solomon Rutzky

Posted Monday, June 29, 2009 7:25 PM

SSC Veteran

Group: General Forum Members
Last Login: Saturday, May 18, 2013 11:12 AM
Points: 285, Visits: 1,377

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# - http://www.SQLsharp.com/

Post #744101

Sam Stange

Posted Tuesday, June 30, 2009 6:19 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, March 25, 2011 9:34 AM
Points: 17, Visits: 67

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

Post #744350

Solomon Rutzky

Posted Tuesday, June 30, 2009 9:41 PM

SSC Veteran

Group: General Forum Members
Last Login: Saturday, May 18, 2013 11:12 AM
Points: 285, Visits: 1,377

Glad you found it helpful. Smile

SQL# - http://www.SQLsharp.com/

Post #745050

massoud-1045248

Posted Monday, March 14, 2011 4:11 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 7:59 AM
Points: 7, Visits: 25

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

Post #1078036

Solomon Rutzky

Posted Tuesday, March 15, 2011 2:05 PM

SSC Veteran

Group: General Forum Members
Last Login: Saturday, May 18, 2013 11:12 AM
Points: 285, Visits: 1,377

massoud-1045248 (3/14/2011)

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# - http://www.SQLsharp.com/

Post #1078637

massoud-1045248

Posted Tuesday, March 15, 2011 2:09 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, December 26, 2012 7:59 AM
Points: 7, Visits: 25

Very much appreciated!
This article has been very useful!
Thanks again

Post #1078638

« Prev Topic | Next Topic »

Permissions