I was playing around with the new SQL 2005 CLR functionality and
remembered this discussion that I had with Erland Sommarskog concerning
performance of scalar UDFs some time ago (See "Calling sp_oa* in
function" in this newsgroup). In that discussion, Erland made the
following comment about UDFs in SQL 2005:

>>The good news is that in SQL 2005, Microsoft has addressed several of
these issues, and the cost of a UDF is not as severe there. In fact for
a complex expression, a UDF in written a CLR language may be faster
than
the corresponding expression using built-in T-SQL functions.<<

I thought the I would put this to the test using some of the same SQL
as before, but adding a simple scalar CLR UDF into the mix. The test
involved querying a simple table with about 300,000 rows. The
scenarios are as follows:

(A) Use a simple CASE function to calculate a column
(B) Use a simple CASE function to calculate a column and as a criterion
in the WHERE clause
(C) Use a scalar UDF to calculate a column
(D) Use a scalar UDF to calculate a column and as a criterion in the
WHERE clause
(E) Use a scalar CLR UDF to calculate a column
(F) Use a scalar CLR UDF to calculate a column and as a criterion in
the WHERE clause

A sample of the results is as follows (time in milliseconds):

(295310 row(s) affected)
A: 1563

(150003 row(s) affected)
B: 906

(295310 row(s) affected)
C: 2703

(150003 row(s) affected)
D: 2533

(295310 row(s) affected)
E: 2060

(150003 row(s) affected)
F: 2190

The scalar CLR UDF function was significantly faster than the classic
scalar UDF, even for this very simple function. Perhaps a more complex
function would have shown even a greater difference. Based on this, I
must conclude that Erland was right. Of course, it's still faster to
stick with basic built-in functions like CASE.

In another test, I decided to run some queries to compare built-in
aggregates vs. a couple of simple CLR aggregates as follows:

(G) Calculate averages by group using the built-in AVG aggregate
(H) Calculate averages by group using a CLR aggregate that similates
the built-in AVG aggregate
(I) Calculate a "trimmed" average by group (average excluding highest
and lowest values) using built-in aggregates
(J) Calculate a "trimmed" average by group using a CLR aggregate
specially designed for this purpose

A sample of the results is as follows (time in milliseconds):

(59 row(s) affected)
G: 313

(59 row(s) affected)
H: 890

(59 row(s) affected)
I: 216

(59 row(s) affected)
J: 846

It seems that the CLR aggregates came with a significant performance
penalty over the built-in aggregates. Perhaps they would pay off if I
were attempting a very complex type of aggregation. However, at this
point I'm going to shy away from using these unless I can't find a way
to do the calculation with standard SQL.

In a way, I'm happy that basic SQL still seems to be the fastest way to
get things done. With the addition of the new CLR functionality, I
suspect that MS may be giving us developers enough rope to comfortably
hang ourselves if we're not careful.

Bill E.
Hollywood, FL
-----------------------------------------------------------------------


-- table TestAssignment, about 300,000 rows
CREATE TABLE [dbo].[TestAssignment](
[TestAssignmentID] [int] NOT NULL,
[ProductID] [int] NULL,
[PercentPassed] [int] NULL,
CONSTRAINT [PK_TestAssignment] PRIMARY KEY CLUSTERED
(
[TestAssignmentID] ASC
)


--Scalar UDF in SQL
CREATE FUNCTION [dbo].[fnIsEven]
(
@intValue int
)
RETURNS bit
AS
BEGIN
Declare @bitReturnValue bit

If @intValue % 2 = 0
Set @bitReturnValue=1
Else
Set @bitReturnValue=0
RETURN @bitReturnValue
END

--Scalar CLR UDF
/*
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{

[Microsoft.SqlServer.Server.SqlFunction(IsDetermini stic=true,IsPrecise=true)]
public static SqlBoolean IsEven(SqlInt32 value)
{
if(value % 2 == 0)
{
return true;
}
else
{
return false;
}
}
};
*/

--Test #1
--Scenario A - Query with calculated column--
SELECT TestAssignmentID,
CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END AS
CalcColumn
FROM TestAssignment

--Scenario B - Query with calculated column as criterion--
SELECT TestAssignmentID,
CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END AS
CalcColumn
FROM TestAssignment
WHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1

--Scenario C - Query using scalar UDF--
SELECT TestAssignmentID,
dbo.fnIsEven(TestAssignmentID) AS CalcColumn
FROM TestAssignment

--Scenario D - Query using scalar UDF as crierion--
SELECT TestAssignmentID,
dbo.fnIsEven(TestAssignmentID) AS CalcColumn
FROM TestAssignment
WHERE dbo.fnIsEven(TestAssignmentID)=1

--Scenario E - Query using CLR scalar UDF--
SELECT TestAssignmentID,
dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumn
FROM TestAssignment

--Scenario F - Query using CLR scalar UDF as crierion--
SELECT TestAssignmentID,
dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumn
FROM TestAssignment
WHERE dbo.fnIsEven(TestAssignmentID)=1


--CLR Aggregate functions
/*
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]
public struct Avg
{
public void Init()
{
this.numValues = 0;
this.totalValue = 0;
}

public void Accumulate(SqlDouble Value)
{
if (!Value.IsNull)
{
this.numValues++;
this.totalValue += Value;
}

}

public void Merge(Avg Group)
{
if (Group.numValues > 0)
{
this.numValues += Group.numValues;
this.totalValue += Group.totalValue;
}

}

public SqlDouble Terminate()
{
if (numValues == 0)
{
return SqlDouble.Null;
}
else
{
return (this.totalValue / this.numValues);
}
}

// private accumulators
private int numValues;
private SqlDouble totalValue;

}

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]
public struct TrimmedAvg
{
public void Init()
{
this.numValues = 0;
this.totalValue = 0;
this.minValue = SqlDouble.MaxValue;
this.maxValue = SqlDouble.MinValue;
}

public void Accumulate(SqlDouble Value)
{
if (!Value.IsNull)
{
this.numValues++;
this.totalValue += Value;
if (Value < this.minValue)
this.minValue = Value;
if (Value > this.maxValue)
this.maxValue = Value;
}


}

public void Merge(TrimmedAvg Group)
{
if (Group.numValues > 0)
{
this.numValues += Group.numValues;
this.totalValue += Group.totalValue;
if (Group.minValue < this.minValue)
this.minValue = Group.minValue;
if (Group.maxValue > this.maxValue)
this.maxValue = Group.maxValue;
}

}

public SqlDouble Terminate()
{
if (this.numValues < 3)
return SqlDouble.Null;
else
{
this.numValues -= 2;
this.totalValue -= this.minValue;
this.totalValue -= this.maxValue;
return (this.totalValue / this.numValues);
}
}

// private accumulators
private int numValues;
private SqlDouble totalValue;
private SqlDouble minValue;
private SqlDouble maxValue;

}
*/

--Test #2

--Scenario G - Average Query using built-in aggregate--
SELECT ProductID, Avg(Cast(PercentPassed AS float))
FROM TestAssignment
GROUP BY ProductID
ORDER BY ProductID

--Scenario H - Average Query using CLR aggregate--
SELECT ProductID, dbo.Avg_CLR(Cast(PercentPassed AS float)) AS Average
FROM TestAssignment
GROUP BY ProductID
ORDER BY ProductID

--Scenario I - Trimmed Average Query using built in aggregates/set
operations--
SELECT A.ProductID,
Case
When B.CountValues<3 Then Null
Else Cast(A.Total-B.MaxValue-B.MinValue AS
float)/Cast(B.CountValues-2 As float)
End AS Average
FROM
(SELECT ProductID, Sum(PercentPassed) AS Total
FROM TestAssignment
GROUP BY ProductID) A

LEFT JOIN

(SELECT ProductID,
Max(PercentPassed) AS MaxValue,
Min(PercentPassed) AS MinValue,
Count(*) AS CountValues
FROM TestAssignment
WHERE PercentPassed Is Not Null
GROUP BY ProductID) B

ON A.ProductID=B.ProductID
ORDER BY A.ProductID

--Scenario J - Trimmed Average Query using CLR aggregate--
SELECT ProductID, dbo.TrimmedAvg_CLR(Cast(PercentPassed AS real)) AS
Average
FROM TestAssignment
GROUP BY ProductID
ORDER BY ProductID

> With the addition of the new CLR functionality, I
> suspect that MS may be giving us developers enough rope to comfortably
> hang ourselves if we're not careful.

Very true. Once size doesn't fit all so one needs to be mindful of the best
tool for the job. The general guideline is that Transact-SQL is best for
data access while CLR is best for moderate to complex computations and
string manipulation.

I haven't played with CLR aggregate functions but it stands to reason that
native Transact-SQL would be faster when the most of the query cost is data
access. I suspect the CLR advantage would kick in when more complex
processing was needed.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<billmiami2@netscape.net> wrote in message
news:1148605648.742461.307520@j55g2000cwa.googlegr oups.com...
>I was playing around with the new SQL 2005 CLR functionality and
> remembered this discussion that I had with Erland Sommarskog concerning
> performance of scalar UDFs some time ago (See "Calling sp_oa* in
> function" in this newsgroup). In that discussion, Erland made the
> following comment about UDFs in SQL 2005:
>
>>>The good news is that in SQL 2005, Microsoft has addressed several of
> these issues, and the cost of a UDF is not as severe there. In fact for
> a complex expression, a UDF in written a CLR language may be faster
> than
> the corresponding expression using built-in T-SQL functions.<<
>
> I thought the I would put this to the test using some of the same SQL
> as before, but adding a simple scalar CLR UDF into the mix. The test
> involved querying a simple table with about 300,000 rows. The
> scenarios are as follows:
>
> (A) Use a simple CASE function to calculate a column
> (B) Use a simple CASE function to calculate a column and as a criterion
> in the WHERE clause
> (C) Use a scalar UDF to calculate a column
> (D) Use a scalar UDF to calculate a column and as a criterion in the
> WHERE clause
> (E) Use a scalar CLR UDF to calculate a column
> (F) Use a scalar CLR UDF to calculate a column and as a criterion in
> the WHERE clause
>
> A sample of the results is as follows (time in milliseconds):
>
> (295310 row(s) affected)
> A: 1563
>
> (150003 row(s) affected)
> B: 906
>
> (295310 row(s) affected)
> C: 2703
>
> (150003 row(s) affected)
> D: 2533
>
> (295310 row(s) affected)
> E: 2060
>
> (150003 row(s) affected)
> F: 2190
>
> The scalar CLR UDF function was significantly faster than the classic
> scalar UDF, even for this very simple function. Perhaps a more complex
> function would have shown even a greater difference. Based on this, I
> must conclude that Erland was right. Of course, it's still faster to
> stick with basic built-in functions like CASE.
>
> In another test, I decided to run some queries to compare built-in
> aggregates vs. a couple of simple CLR aggregates as follows:
>
> (G) Calculate averages by group using the built-in AVG aggregate
> (H) Calculate averages by group using a CLR aggregate that similates
> the built-in AVG aggregate
> (I) Calculate a "trimmed" average by group (average excluding highest
> and lowest values) using built-in aggregates
> (J) Calculate a "trimmed" average by group using a CLR aggregate
> specially designed for this purpose
>
> A sample of the results is as follows (time in milliseconds):
>
> (59 row(s) affected)
> G: 313
>
> (59 row(s) affected)
> H: 890
>
> (59 row(s) affected)
> I: 216
>
> (59 row(s) affected)
> J: 846
>
> It seems that the CLR aggregates came with a significant performance
> penalty over the built-in aggregates. Perhaps they would pay off if I
> were attempting a very complex type of aggregation. However, at this
> point I'm going to shy away from using these unless I can't find a way
> to do the calculation with standard SQL.
>
> In a way, I'm happy that basic SQL still seems to be the fastest way to
> get things done. With the addition of the new CLR functionality, I
> suspect that MS may be giving us developers enough rope to comfortably
> hang ourselves if we're not careful.
>
> Bill E.
> Hollywood, FL
> -----------------------------------------------------------------------
>
>
> -- table TestAssignment, about 300,000 rows
> CREATE TABLE [dbo].[TestAssignment](
> [TestAssignmentID] [int] NOT NULL,
> [ProductID] [int] NULL,
> [PercentPassed] [int] NULL,
> CONSTRAINT [PK_TestAssignment] PRIMARY KEY CLUSTERED
> (
> [TestAssignmentID] ASC
> )
>
>
> --Scalar UDF in SQL
> CREATE FUNCTION [dbo].[fnIsEven]
> (
> @intValue int
> )
> RETURNS bit
> AS
> BEGIN
> Declare @bitReturnValue bit
>
> If @intValue % 2 = 0
> Set @bitReturnValue=1
> Else
> Set @bitReturnValue=0
> RETURN @bitReturnValue
> END
>
> --Scalar CLR UDF
> /*
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
>
> public partial class UserDefinedFunctions
> {
>
> [Microsoft.SqlServer.Server.SqlFunction(IsDetermini stic=true,IsPrecise=true)]
> public static SqlBoolean IsEven(SqlInt32 value)
> {
> if(value % 2 == 0)
> {
> return true;
> }
> else
> {
> return false;
> }
> }
> };
> */
>
> --Test #1
> --Scenario A - Query with calculated column--
> SELECT TestAssignmentID,
> CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END AS
> CalcColumn
> FROM TestAssignment
>
> --Scenario B - Query with calculated column as criterion--
> SELECT TestAssignmentID,
> CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END AS
> CalcColumn
> FROM TestAssignment
> WHERE CASE WHEN TestAssignmentID % 2=0 THEN 1 ELSE 0 END=1
>
> --Scenario C - Query using scalar UDF--
> SELECT TestAssignmentID,
> dbo.fnIsEven(TestAssignmentID) AS CalcColumn
> FROM TestAssignment
>
> --Scenario D - Query using scalar UDF as crierion--
> SELECT TestAssignmentID,
> dbo.fnIsEven(TestAssignmentID) AS CalcColumn
> FROM TestAssignment
> WHERE dbo.fnIsEven(TestAssignmentID)=1
>
> --Scenario E - Query using CLR scalar UDF--
> SELECT TestAssignmentID,
> dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumn
> FROM TestAssignment
>
> --Scenario F - Query using CLR scalar UDF as crierion--
> SELECT TestAssignmentID,
> dbo.fnIsEven_CLR(TestAssignmentID) AS CalcColumn
> FROM TestAssignment
> WHERE dbo.fnIsEven(TestAssignmentID)=1
>
>
> --CLR Aggregate functions
> /*
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
>
>
> [Serializable]
> [Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]
> public struct Avg
> {
> public void Init()
> {
> this.numValues = 0;
> this.totalValue = 0;
> }
>
> public void Accumulate(SqlDouble Value)
> {
> if (!Value.IsNull)
> {
> this.numValues++;
> this.totalValue += Value;
> }
>
> }
>
> public void Merge(Avg Group)
> {
> if (Group.numValues > 0)
> {
> this.numValues += Group.numValues;
> this.totalValue += Group.totalValue;
> }
>
> }
>
> public SqlDouble Terminate()
> {
> if (numValues == 0)
> {
> return SqlDouble.Null;
> }
> else
> {
> return (this.totalValue / this.numValues);
> }
> }
>
> // private accumulators
> private int numValues;
> private SqlDouble totalValue;
>
> }
>
> [Serializable]
> [Microsoft.SqlServer.Server.SqlUserDefinedAggregate (Format.Native)]
> public struct TrimmedAvg
> {
> public void Init()
> {
> this.numValues = 0;
> this.totalValue = 0;
> this.minValue = SqlDouble.MaxValue;
> this.maxValue = SqlDouble.MinValue;
> }
>
> public void Accumulate(SqlDouble Value)
> {
> if (!Value.IsNull)
> {
> this.numValues++;
> this.totalValue += Value;
> if (Value < this.minValue)
> this.minValue = Value;
> if (Value > this.maxValue)
> this.maxValue = Value;
> }
>
>
> }
>
> public void Merge(TrimmedAvg Group)
> {
> if (Group.numValues > 0)
> {
> this.numValues += Group.numValues;
> this.totalValue += Group.totalValue;
> if (Group.minValue < this.minValue)
> this.minValue = Group.minValue;
> if (Group.maxValue > this.maxValue)
> this.maxValue = Group.maxValue;
> }
>
> }
>
> public SqlDouble Terminate()
> {
> if (this.numValues < 3)
> return SqlDouble.Null;
> else
> {
> this.numValues -= 2;
> this.totalValue -= this.minValue;
> this.totalValue -= this.maxValue;
> return (this.totalValue / this.numValues);
> }
> }
>
> // private accumulators
> private int numValues;
> private SqlDouble totalValue;
> private SqlDouble minValue;
> private SqlDouble maxValue;
>
> }
> */
>
> --Test #2
>
> --Scenario G - Average Query using built-in aggregate--
> SELECT ProductID, Avg(Cast(PercentPassed AS float))
> FROM TestAssignment
> GROUP BY ProductID
> ORDER BY ProductID
>
> --Scenario H - Average Query using CLR aggregate--
> SELECT ProductID, dbo.Avg_CLR(Cast(PercentPassed AS float)) AS Average
> FROM TestAssignment
> GROUP BY ProductID
> ORDER BY ProductID
>
> --Scenario I - Trimmed Average Query using built in aggregates/set
> operations--
> SELECT A.ProductID,
> Case
> When B.CountValues<3 Then Null
> Else Cast(A.Total-B.MaxValue-B.MinValue AS
> float)/Cast(B.CountValues-2 As float)
> End AS Average
> FROM
> (SELECT ProductID, Sum(PercentPassed) AS Total
> FROM TestAssignment
> GROUP BY ProductID) A
>
> LEFT JOIN
>
> (SELECT ProductID,
> Max(PercentPassed) AS MaxValue,
> Min(PercentPassed) AS MinValue,
> Count(*) AS CountValues
> FROM TestAssignment
> WHERE PercentPassed Is Not Null
> GROUP BY ProductID) B
>
> ON A.ProductID=B.ProductID
> ORDER BY A.ProductID
>
> --Scenario J - Trimmed Average Query using CLR aggregate--
> SELECT ProductID, dbo.TrimmedAvg_CLR(Cast(PercentPassed AS real)) AS
> Average
> FROM TestAssignment
> GROUP BY ProductID
> ORDER BY ProductID
>

On 25 May 2006 18:07:28 -0700, billmiami2@netscape.net wrote:

>I thought the I would put this to the test using some of the same SQL
>as before, but adding a simple scalar CLR UDF into the mix.

Hi Bill,

Thanks for testing this, and for sharing the results. Very enlightening.

Just a quick note -

>--Scenario I - Trimmed Average Query using built in aggregates/set
>operations--
>SELECT A.ProductID,
> Case
> When B.CountValues<3 Then Null
> Else Cast(A.Total-B.MaxValue-B.MinValue AS
>float)/Cast(B.CountValues-2 As float)
> End AS Average
>FROM
> (SELECT ProductID, Sum(PercentPassed) AS Total
> FROM TestAssignment
> GROUP BY ProductID) A
>
>LEFT JOIN
>
> (SELECT ProductID,
> Max(PercentPassed) AS MaxValue,
> Min(PercentPassed) AS MinValue,
> Count(*) AS CountValues
> FROM TestAssignment
> WHERE PercentPassed Is Not Null
> GROUP BY ProductID) B
>
>ON A.ProductID=B.ProductID
>ORDER BY A.ProductID

I think that this can be simplified to

SELECT ProductID,
CASE WHEN COUNT(*) >= 3
THEN CAST(SUM(PercentPassed) - MAX(PercentPassed) -
MIN(PercentPassed) AS float) / CAST(COUNT(*) - 2 AS float)
END AS Average
FROM TestAssignment
WHERE PercentPassed IS NOT NULL
GROUP BY ProductID
ORDER BY ProductID

A few quick tests show no differences with your version, and approx. 50%
less execution time.

--
Hugo Kornelis, SQL Server MVP

(billmiami2@netscape.net) writes:
> The scalar CLR UDF function was significantly faster than the classic
> scalar UDF, even for this very simple function. Perhaps a more complex
> function would have shown even a greater difference. Based on this, I
> must conclude that Erland was right. Of course, it's still faster to
> stick with basic built-in functions like CASE.

Yes, in this simple case.

During the beta of SQL 2005 I ran a test where I tested a CLR UDF, T-SQL UDf
and an expression with T-SQL builtins only. Of these the CLR UDF was the
fastest. The operation in this case was more complex, and included
convert(float, ), power(), substring and a CASE in T-SQL.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Hugo,

I think that I tried your expression but it didn't give me exactly the
same result because it eliminated the groupings without any counts,
which was not the case for the CLR version. I wanted to make sure that
the two resultsets were exactly the same, so I chose my more
complicated expression. I did try an expression with a single query
that gave me the same results (including the Null groupings) which was

SELECT ProductID,
CASE WHEN COUNT(PercentPassed) >= 3
THEN CAST(SUM(PercentPassed) - MAX(PercentPassed) -
MIN(PercentPassed) AS float) / CAST(COUNT(PercentPassed) - 2 AS float)
END AS Average
FROM TestAssignment
GROUP BY ProductID
ORDER BY ProductID

but this took more time to run than my expression with the subqueries,
so I abandoned it. Even so, choosing one vs. another does not change
the overall conclusions of the test.

Erland,

By how much were the CLR UDFs faster than the expressions using
built-in functions? Was the difference enough to motivate you to start
writing CLR UDFs in the future, even though you would create
dependencies on the DLLs that you otherwise would not have?

Also, did you find any cases where a CLR aggregate gave you any
benefit?

Bill

(billmiami2@netscape.net) writes:
> By how much were the CLR UDFs faster than the expressions using
> built-in functions? Was the difference enough to motivate you to start
> writing CLR UDFs in the future, even though you would create
> dependencies on the DLLs that you otherwise would not have?

The difference was significant. (I don't feel like disclosing any exact
numbers, because this was test with beta software, so the numbers are not
relevant for the product today, and disclosing numbers may be compliant
with the license agreement for the beta.)

Since our product will support SQL 2000 for at least our up-coming
version as well, I have not really started thinking of how much we
will use the CLR. But I have at least one function that I'm pretty
sure that I will rewrite in C#: this UDF performs "intelligent" rounding
of floats. This involves converting from float to string to decimal
and back to float.

We will get the CLR in our product anyway, as we today have a couple
of extented stored procedures, and a COM module that we call from
SQL Server. These we plan to rewrite. This means that we will need
to hand CLR modules in our build process. (And I am quite sure that
that will not involve Visual Studio.)

Note also, that there are *no* dependencies on DLLs at run time. The
assemblies are stored within SQL Server.

> Also, did you find any cases where a CLR aggregate gave you any
> benefit?

I have not explored CLR aggregates very closely, and I have not seen
any apparent scenarios where they come in handy for us.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland,

Thanks for the reply. I certainly see how rewriting the extended
stored procedures would be beneficial and would almost be a slam dunk
in favor of the CLR. I did one just for fun that calls a web service,
passes parameters and displays the resulting dataset as a resultset.
This is probably not something that I would do in an production
environment, but it certainly opened my mind to the possibilities.

I'm aware that the assemblies are stored in SQL Server upon running
CREATE ASSEMBLY so there is no direct dependency. However,the
assemblies must initially be made accessible to the SQL Server so that
it can bind to them. Also, if an assembly is changed, the revised file
must be again made available, so you're still having to manage files
that you wouldn't need to manage had you used T-SQL only.

Can you foresee using some of the new T-SQL enhancements in your
product? I have a couple of projects that involve hierarchies and the
recursive Common Table Expressions and Cross/Outer Apply have made for
far simpler queries than I could have created in SQL 2000. I haven't
used them in production yet, but the ranking functions look very nice
too.

Bill

(billmiami2@netscape.net) writes:
> I'm aware that the assemblies are stored in SQL Server upon running
> CREATE ASSEMBLY so there is no direct dependency. However,the
> assemblies must initially be made accessible to the SQL Server so that
> it can bind to them. Also, if an assembly is changed, the revised file
> must be again made available, so you're still having to manage files
> that you wouldn't need to manage had you used T-SQL only.

Actually not. You can load assemblies as hexstrings as well. Try
Script As in Mgmt Studio on an assembly to see what I'm talking about.

What would be very nice is something like:

CREATE PROCEDURE my_sharp_proc @a int, @b int WITH LANGUAGE C# AS

and the rest of the batch would be the procedure definition in C#. SQL
Server would compile it on the server, and create an assembly and all
that jazz for you.

> Can you foresee using some of the new T-SQL enhancements in your
> product? I have a couple of projects that involve hierarchies and the
> recursive Common Table Expressions and Cross/Outer Apply have made for
> far simpler queries than I could have created in SQL 2000. I haven't
> used them in production yet, but the ranking functions look very nice
> too.

The absolutely biggest improvement in SQL 2005 is the error handling.
I'm considering modifying our load tool so that if there is no BEGIN
TRY in the code, the load tool would splice in BEGIN TRY in the
beginning of the procedure (it splices in SET NOCOUNT ON today), and
then tack on an standard error handler at the end.

We have also discussed improving our auditing (which is not very strong
today). To this end the xml data type can be very useful. You can write
standard trigger that transforms the contents of inserted to one XML
document per row, and the store that in a common log table. We would then
write a generic log reader on top of this.

I would also expect row_number() to very useful. We don't work that
much with hierarchies, but certainly there will be one or two CTEs.

I have hardly looked at Service Broker myself, but there are some
people who developing new stuff that will use Service Broker. (This is
for a new customer that will run SQL 2005 from day one.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Erland,

> Actually not. You can load assemblies as hexstrings as well. Try
> Script As in Mgmt Studio on an assembly to see what I'm talking about.
>
> What would be very nice is something like:
>
> CREATE PROCEDURE my_sharp_proc @a int, @b int WITH LANGUAGE C# AS
>
> and the rest of the batch would be the procedure definition in C#. SQL
> Server would compile it on the server, and create an assembly and all
> that jazz for you.


Hey--that's great! Thanks for pointing that out to me. It will save
me from having to copy the assemblies to the production server. Your
modified CREATE PROCEDURE would be even better but I suppose we'll need
to wait for SQL Server 2010 (?) for that.

Yes, the error trapping is quite a nice addition. Perhaps your
standard error handler can write to your new XML log and send you an
e-mail if the severity level is a certain level.

I'm just now experimenting with all of the new XML capabilities.and I'm
trying to see where I might put them to use. I haven't yet covered the
Service Broker. I have written one SSIS package that is now running in
production, but I must admit that it was far more complicated to use
than DTS, at least for simple things.

One thing that disappointed me was the PIVOT function. It seems that
the column names must be hard coded. I suppose that I can get around
this by using dynamic SQL or by writing a CLR procedure that calculates
the column names, but this is what I was hoping to avoid. If you know
a clever way around this, I'd love to hear it.

There's so much new ground to cover, it's quite amazing.

Bill

Erland Sommarskog wrote:
> (billmiami2@netscape.net) writes:
> > I'm aware that the assemblies are stored in SQL Server upon running
> > CREATE ASSEMBLY so there is no direct dependency. However,the
> > assemblies must initially be made accessible to the SQL Server so that
> > it can bind to them. Also, if an assembly is changed, the revised file
> > must be again made available, so you're still having to manage files
> > that you wouldn't need to manage had you used T-SQL only.
>
> Actually not. You can load assemblies as hexstrings as well. Try
> Script As in Mgmt Studio on an assembly to see what I'm talking about.
>
> What would be very nice is something like:
>
> CREATE PROCEDURE my_sharp_proc @a int, @b int WITH LANGUAGE C# AS
>
> and the rest of the batch would be the procedure definition in C#. SQL
> Server would compile it on the server, and create an assembly and all
> that jazz for you.
>
> > Can you foresee using some of the new T-SQL enhancements in your
> > product? I have a couple of projects that involve hierarchies and the
> > recursive Common Table Expressions and Cross/Outer Apply have made for
> > far simpler queries than I could have created in SQL 2000. I haven't
> > used them in production yet, but the ranking functions look very nice
> > too.
>
> The absolutely biggest improvement in SQL 2005 is the error handling.
> I'm considering modifying our load tool so that if there is no BEGIN
> TRY in the code, the load tool would splice in BEGIN TRY in the
> beginning of the procedure (it splices in SET NOCOUNT ON today), and
> then tack on an standard error handler at the end.
>
> We have also discussed improving our auditing (which is not very strong
> today). To this end the xml data type can be very useful. You can write
> standard trigger that transforms the contents of inserted to one XML
> document per row, and the store that in a common log table. We would then
> write a generic log reader on top of this.
>
> I would also expect row_number() to very useful. We don't work that
> much with hierarchies, but certainly there will be one or two CTEs.
>
> I have hardly looked at Service Broker myself, but there are some
> people who developing new stuff that will use Service Broker. (This is
> for a new customer that will run SQL 2005 from day one.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx

(billmiami2@netscape.net) writes:
> One thing that disappointed me was the PIVOT function. It seems that
> the column names must be hard coded. I suppose that I can get around
> this by using dynamic SQL or by writing a CLR procedure that calculates
> the column names, but this is what I was hoping to avoid. If you know
> a clever way around this, I'd love to hear it.

A lot of people is screaming for a dynamic PIVOT, but my prediction is
that you will never be able to do this in a SELECT statement.

The reason for this is that a SELECT statement returns table, and a table
should have a fixed set of columns. This is presumption to make it possible
to reuse a SELECT statement in a view or a derived table.

The way around this would be to introduce a new statement that in difference
to SELECT is not composable, but else would have most of the features of
SELECT.

Myself, I only very rarely has the need to run pivot queries.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx