Implementing CLR User defined Scalar Functions in SQL server

If you want know how to create a SQL CLR project in Visual studio

look at this post

lets create a scalar function

What is_deterministic , Userdataaccess, Systemdataaccess please use below links

Property Explanation
IsDeterministic : True if the function will always return the same value, if called with the same parameters. False if the function                                                       can return different values when called with the same parameters. An example of a deterministic function is the SayHello function.                                                                   An example of a nondeterministic function is a function that will use database data that varies.

DataAccess Specifies whether the function will read data from the context SQL Server connection. Acceptable valuesare DataAccessKind.None and DataAccessKind.Read.

SystemDataAccess Specifies whether the function will require access to system catalogs or system tables in SQL Server—for
example, sys.object. Acceptable values are System DataAccessKind.None and SystemDataAccessKind.Read.

IsPrecise Specifies whether the function will be performing imprecise computation, for example, float operations.True if the function does not involve these
operations, false if it does. SQL Server uses this information for indexing.

FillRowMethodName Used for table-valued functions. This is the name of a method in the same class that serves as the
table-valued function contract.

TableDefinition Used for table-valued functions. Defines the column list in the table returned.

Name The name of the SQL function.

Use the .DLL and register the assembly in sql server  and create a function


















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(DataAccess= DataAccessKind.None,
IsDeterministic= true,SystemDataAccess=SystemDataAccessKind.None,IsPrecise=true)]
public static SqlInt32 ScalarUdf(SqlInt32 x, SqlInt32 y)

SqlInt32 z = x + y;

return (z);


USE [test]
FROM ‘C:\Users\akhil\Documents\Visual Studio 2010\Projects\SQL CLR\CLR function\bin\Debug\CLR function.dll’



create function sumxy (@x int, @y int)

returns int


external name [CLR function].UserDefinedFunctions.ScalarUdf



select dbo.sumxy(1,10)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s