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 12»»

Getting The Most Out of SQL Server 2005 UDTs and UDAs Expand / Collapse
Author
Message
Posted Thursday, August 23, 2007 10:02 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:10 PM
Points: 368, Visits: 1,948
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/srutzky/3208.asp




SQL# - http://www.SQLsharp.com/
Post #393364
Posted Wednesday, September 19, 2007 8:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:01 PM
Points: 1,113, Visits: 705
"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
Posted Monday, January 7, 2008 12:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, June 29, 2009 11:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, June 29, 2009 7:25 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:10 PM
Points: 368, Visits: 1,948
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
Posted Tuesday, June 30, 2009 6:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, June 30, 2009 9:41 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:10 PM
Points: 368, Visits: 1,948
Glad you found it helpful.




SQL# - http://www.SQLsharp.com/
Post #745050
Posted Monday, March 14, 2011 4:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:43 PM
Points: 8, Visits: 29
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
Posted Tuesday, March 15, 2011 2:05 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, September 27, 2014 8:10 PM
Points: 368, Visits: 1,948
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# - http://www.SQLsharp.com/
Post #1078637
Posted Tuesday, March 15, 2011 2:09 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 25, 2014 11:43 PM
Points: 8, Visits: 29
Very much appreciated!
This article has been very useful!
Thanks again
Post #1078638
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse