Managed C++ User-defined Aggregate Function

Hi all,

I'm attempting to write an aggregate function in C++ to compare performance with the equivalent function in C#.

However, I'm having problems getting SQL Server to see the function in the assembly. It allows me to load the assembly into the database, but I can't see the type in it.

Here's my code:

// CPPTest.h

#pragma once

using namespace System;
using namespace Microsoft::SqlServer::Server;
using namespace System::Data::SqlTypes;
using namespace System::Data::SqlClient;

namespace CPPTest {

[Serializable]
[Microsoft::SqlServer::Server::SqlUserDefinedAggregate(
Format::Native,
Name="AGG_CPP_OR")]
public ref struct AGG_CPP_OR
{
public:
void Init();
void Accumulate(SqlInt32 Value);
void Merge(AGG_CPP_OR^ Group);
SqlInt32 Terminate();

private:
SqlInt32 m_accum;
};

}

// CPPTest.cpp

#include "stdafx.h"

#include "CPPTest.h"

void CPPTest::AGG_CPP_OR::Init()
{
m_accum = 0;
}

void CPPTest::AGG_CPP_OR::Accumulate(SqlInt32 Value)
{
m_accum = m_accum | Value;
}

void CPPTest::AGG_CPP_OR::Merge(CPPTest::AGG_CPP_OR^ Group)
{
m_accum = m_accum | Group->m_accum;
}

SqlInt32 CPPTest::AGG_CPP_OR::Terminate()
{
return m_accum;
}

Compile it with /clr:safe option and it can be loaded as an assembly into SQL Server 2005 (9.0.1399), but the AGG_CPP_OR type is not seen as an aggregate function. I've also tried implementing IBinarySerialize and setting Format to Format::UserDefined (and putting in MaxByteSize) but it makes no difference.

Does anyone know what I'm missing here?

Many thanks,OK, take it out of the CPPTest namespace and it can be added as an aggregate function with

CREATE AGGREGATE BITWISE_OR(@.input int)
RETURNS int
EXTERNAL NAME [CPPTest].[AGG_CPP_OR];
GO

Now it's a dependancy issue stopping it from running, but I'll keep on it.

Thanks,

Make Sql Function to modify data like Sql Procedure to improve code structure and readability.

Now Sql Function can not modify data as Sql Procedure like other database, it's very troublesome at most case!

in most case, Sql Function is used to improve code structure then it can be maintanced easy! BUT only because it can not modify data, there are 2 troublesome way:

1. Make all callers in the path from Sql Functions to Sql Procedure. and the coder will cry, the code will become very

confusional , and very difficult to maintance.

2. Divide the Sql Function into a thin CLR wrapper to call a Sql Procedure, can only use another connection, BUT can not be in the same transaction context And the code is ugly and slow.

You should not give limitation to Sql Function, should just limit the using context of Sql Function!

The sql code is more difficult to read and maintance than norm code(C#), then the improving code structure and readability should be one of your most important task, that's why microsoft!

I won’t agree with your point here.

1. Code structure and readability might vary person to person. If you are a C# programmer you might find the SQL programming looks difficult, the same db programmer sees the C# code.

2. The programming languages or script languages are not considered “How your code structured and readability” they only about how efficiently you can code. The simple lines of code need not to be efficient than the huge lines of code. Readability and structuring the code is up to the coder/programmer. It is not one of the consideration on compiler or language to choose.

Function Restriction:

These are used to limit the boundary, and allow the users to experience good performance in any kind of scenario. All the languages has its own boundary and limitations.

SQL Server never allows a Insert/Update/Delete statements inside your function (on your DB objects). Its makes sense, because functions are different from sps. SPs are the batches which will be invoked from the single point (using Exec…). But function are called from your SQL statements (like Insert/Update/Delete/Select). Lets say when you try to call the select statement why you want to do any data medication on your database tables. It is controversial with accepted SQL behavior.

Finally the Code readable & structured is upto you (who creates) & who read the code. It is widly accepted behavior in the database(take any database).

|||

Hi!

Do you mean it should work like functions in PL/SQL ;^)

/Bjorn

|||

Hi,

Manivannan.D.Sekaran, I do not think you are right. Bjorn is right!

If you have use DB2 and Oracle, or you have writen much more complex sql code, you will not say it like so. you can not say: I do design it like so because it may cause low performance, even if it can not complete some common task. you should has task like that! I do not think let sql Function update data will cause low performance in most case. if it's true, how do DB2 and Oracle live?

User's requirement is the most important thing! all other is under it.

|||

I agree. In PL-SQL you can't have SELECT statements inside your SP. If you Select you have to supply the INTO clause rite? But in SQL Server you can create a wrapper for (even) your select statement(s).

I am not here to argue whether SQL Server is best or Oracle is not, I am just wanted to explain the approach how it will affect your performance when you want to do data manipulation on Functions. Bcs every database engine has its own behaviour....

Why SQL Server missed might be big Qs?

But To fix this limitaion we have the .NET integration now (you have work-around).

|||

Hi, i agree, every database engine has its owner behaviour! and every database has it's advantage over others, BUT, i think the advantage of sqlserver is easy to use! yes, i have work-around, but you think the work-around is satisfactorily? On the one hand, it add complexity, i have to write a thin wrapper just for this, on the other hand, i can not use the same transaction context, this will cause error at some case. the last it will slow down!!!

just now , you said "Lets say when you try to call the select statement why you want to do any data medication on your database tables", then I give a example to use sql function:

I hava a complex calculate on amount of entries which depend on volatile data, in order to improve perfomanace, the best way is to add cache, and use lazy calculation. if sql function support modify data( add to the cache if not there) , everything is simple. Now, it's not there, many sp and function use this cache has to be rewrited, in my system, there are handrens sp and function use this "base function", now you know what the task is. i have to change all function to sp, and add some sp_PrepareXXX + fn_GetXXX, it's very troublesome!!! and the whole consitent code was changed to very urgly code!

Another if i use founction first and wirte thousands sql function and sp depend on it, but later i want to change the implementation to provide some lazy algorithm(has to update data) to improve performance, what i can do is cry! BUT this will not happen in norm coding(e,g, C#).

To user's view, should not has sp exists, the function is enought, although every database has it's limitation,BUT this is the final goal of computer industry, is it? I do think microsoft will do much better job then us if they do it in database system level--both in performance or in syntax, although it may has some shortcoming, it should give a way to handle complex case.

thanks for u reply, just for technology.

|||

Hi Manivannan!

I would become really happy if MS extend the (T)SQL language,

providing the feature of functions we are talking about here.

It would also be very nice if you support TSQL-packages, ANSI-XML-features in SQL

and naming overloading.

/Bjorn

Make C#

Can somebody help me convert this SQL2000 Function TO C# Function please

CREATE Function dbo.CalculateNextRentDate ( @.Rent_Payment_Date datetime, @.Frequency varchar(50) , @.Number int)

RETURNS DATETIME

AS
begin

DECLARE @.NextPaymentDate Datetime
SET @.Number = @.Number - 1
.
IF @.Frequency = 'month'
IF @.rent_payment_date = dateadd(month, datediff(month, 0+@.Number, @.rent_payment_date) + 1, -1)
BEGIN
SET @.NextPaymentDate = dateadd(month, datediff(month, 0, @.rent_payment_date) + 2, -1)
END
ELSE
BEGIN
SET @.NextPaymentDate = dateadd(month, 1+@.Number, @.rent_payment_date)
END

return @.NextPaymentDate
end

I can't understand why in the T-SQL function it first checks whether the @.Rent_Payment_Date is the last day of the month, so I just remove the check and here is the code:

public DateTime CalculateNextRentDate(DateTime Rent_Payment_Date, string Frequency, int Number)
{
DateTime NextPaymentDate;
NextPaymentDate = Rent_Payment_Date;
Number--;
if (Frequency == "month")
{
NextPaymentDate = Rent_Payment_Date.AddMonths(Number);
}
return NextPaymentDate;
}