B) CLR Table-valued Functions (TVF)
A table-valued function (TVF) is a user-defined function that returns a table. Data is returned from a TVF through an IEnumberable or IEnumerator object.
Transact-SQL TVFs materialize the results of calling the function into an intermediate table. While CLR TVFs use the streaming alternative. The entire set of results is not materialized into a single table. The streaming model helps the result to be consumed immediately after the first row is available. We do not have to wait for the entire table to be populated. This is an advantage over the TSQL TVFs.
CLR TVFs can be implemented by using the IEnumberable Interface.
Example:
using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;
using System.Diagnostics;
public class TabularEventLog
{
[SqlFunction(FillRowMethodName = “FillRow”)]
public static IEnumerable InitMethod(String logname)
{
return new EventLog(logname, Environment.MachineName).Entries;
}
public static void FillRow(Object obj, out SqlDateTime timeWritten, out SqlChars message, out SqlChars category, out long instanceId)
{
EventLogEntry eventLogEntry = (EventLogEntry)obj;
timeWritten = new SqlDateTime(eventLogEntry.TimeWritten);
message = new SqlChars(eventLogEntry.Message);
category = new SqlChars(eventLogEntry.Category);
instanceId = eventLogEntry.InstanceId;
}
}
In the above code,the information from eventlog is presented as table to the user.
Compile to generate “SampleTVF_CLR.dll”.
Note:-A TVF can only perform data access through a Transact-SQL query in the InitMethod method, and not in the FillRow method. The InitMethod should be marked with the SqlFunction.DataAccess.Read attribute property if a Transact-SQL query is performed.
Now we will build the assembly and create the function in SQL Server.This function will use the TabularEventLog’s Initmethod.
CREATE ASSEMBLY tvfEventLog
FROM’C:WINDOWSMicrosoft.NETFrameworkv2.0.50727SampleTVF_CLR.dll’
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION ReadEventLog(@logname nvarchar(100))
RETURNS TABLE
(logTime datetime,Message nvarchar(4000),Category nvarchar(4000),InstanceId bigint)
AS
EXTERNAL NAME tvfEventLog.TabularEventLog.InitMethod
GO
C) User Defined Aggregates
Aggregate functions perform a calculation on a set of values and return a single value. SQL Server has only two built-in aggregate functions, such as SUM or MAX, that operate on a set of input scalar values and generate a single aggregate value from that set. SQL Server integration with the Microsoft .NET Framework common language runtime (CLR) now allows developers to create custom aggregate functions in managed code, and to make these functions accessible to Transact-SQL or other managed code.
A type in a common language runtime (CLR) assembly can be registered as a user-defined aggregate function, as long as it implements the required aggregation contract. This contract consists of the SqlUserDefinedAggregate attribute and the aggregation contract methods. The aggregation contract includes the mechanism to save the intermediate state of the aggregation, and the mechanism to accumulate new values, which consists of four methods: Init, Accumulate, Merge, and Terminate.
SqlUserDefinedAggregate Attribute
A user-defined aggregate provides the query processor additional information about the properties of the aggregation algorithm. There are two required properties on the SqlUserDefinedAggregate attribute that control the serialization format used.They are:
Format: – The serialization format for this type.
MaxByteSize:- The maximum size in bytes needed to store the state for this aggregate during computation.
The query optimizer can use other properties (IsInvariantToDuplicates, IsInvariantToNulls, IsInvariantToOrder, and IsNullIfEmpty) to search for more efficient query execution plans.These properties have to be specified as part of the SqlUserDefinedAggregate attribute definition on the type. By default, all of these properties are set to false.
Aggregation Methods
a)Init
The query processor uses this method to initialize the computation of the aggregation. This method is invoked once for each group that the query processor is aggregating. It reinitializes when SQL Server chooses to reuse an aggregate class instead of creating a new one.
public void Init(); /* needed for empty group */
b)Accumulate
The query processor uses this method to accumulate the aggregate values. This is invoked once for each value in the group that is being aggregated.
public void Accumulate ( input-type value);
c)Merge
This method can be used to merge another instance of this aggregate class with the current instance.
public void Merge( udagg_class value);
d)Terminate
This method completes the aggregate computation and returns the result of the aggregation.
public return_type Terminate();
User Defined Procedures
In the common language runtime (CLR), stored procedures are implemented as public static methods on a class in a Microsoft .NET Framework assembly. The static method can either be declared as void, or return an integer value. If it returns an integer value, the integer returned is treated as the return code from the procedure. For example:
EXECUTE @return_status = procedure_name
The @return_status variable will contain the value returned by the method. If the method is declared void, the return code is 0.If the method takes parameters, the number of parameters in the .NET Framework implementation should be the same as the number of parameters used in the Transact-SQL declaration of the stored procedure.
Writing “Hello World” CLR stored procedure
1) Type the following C# code in a notepad and save it as “HelloWorld.cs”.
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
public class HelloWorldProc
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void HelloWorld()
{
SqlContext.Pipe.Send(“Hello world!n”);
}
}
Returning messages to the client is done through the SqlPipe object, which is obtained by using the Pipe property of the SqlContext class. The SqlPipe object has a Send method. By calling the Send method, the user can transmit data through the pipe to the calling application.
Compile the above file using the command line compiler for Visual C# (csc.exe).
The following command is used to compile the HelloWorld.cs file,
csc /target: library helloworld.cs
2) The next step is to load and run the “HelloWorld” stored procedure in SQL Server. The ability to execute common language runtime (CLR) code is set to OFF by default in SQL Server 2005. The CLR code can be enabled by using the sp_configure system stored procedure. Refer to section “Enabling CLR Integration”.
We will need to create the assembly so we can access the stored procedure. This can be done using CREATE ASSEMBLY TSQL command.
CREATE ASSEMBLY helloworld from ‘C:WINDOWSMicrosoft.NETFrameworkv2.0.50727HelloWorld.dll’ WITH PERMISSION_SET = SAFE
Once the assembly has been created, we can now access our HelloWorld method by using the create procedure statement.
CREATE PROCEDURE hello
AS
EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld
Now we can test the procedure by executing the EXEC statement.
Extended Procedures vs. Managed code
Extended stored procedures can be built to perform functionality not possible with Transact-SQL stored procedures. Extended stored procedures can, however, compromise the integrity of the SQL Server process, while managed code that is verified to be type-safe cannot. Further, memory management, scheduling of threads and fibers, and synchronization services are more deeply integrated between the managed code of the CLR and SQL Server. With CLR integration, the user can have a more secure way than extended stored procedures.