Click here to Skip to main content
13,354,886 members (70,104 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


4 bookmarked
Posted 9 Nov 2013

Getting Started With SQL Server 2012 CLR User Defined Functions

, 9 Nov 2013
Rate this:
Please Sign up or sign in to vote.
A quick tutorial on how to do implement CLR user defined functions.


When I’m on a data project such as using SSIS (SQL Server Integration Services) to perform ETL tasks I try to keep my code in SQL as much as possible because typically that’s the strongest skillset of someone that works with SSIS however sometimes that is not good enough.  If you run across something that is just not practical to do in SQL Server via straight T-SQL one solution can be using a CLR user defined function, problem with this is it is something that you probably don’t do very often so you may forget the specifics on how this works.  Here is a quick tutorial on how to do implement CLR user defined functions that you can bookmark to hopefully save you from searching the web next time you need to do this.


A User Defined Function (or UDF for short) is exactly what it says, some function callable in SQL that is not part of the system functions.  Great examples of this are when you are implementing system functionality that may exist in another database server to yours.  Or if you have some complex algorithm that you want to re-use throughout the database, 


  • SQL Server 2012
  • Visual Studio 2012 Premium
  • SQL Server 2012 Data Tools – Business Intelligence for Visual Studio 2012
  • Windows 8


First we want to create a new SQL Server Database Project, for this example I’ll call it simply “UDF_Blog”


Once you have a project lets create two new SQL CLR C# User Defined Functions at the root of the project:

  • ScalarUDF.cs
  • TableUDF.cs


That’s it! we have a new C# file for each of the two examples that I will be explaining.

UDF Examples

Example 1: Scalar-valued UDF

A Scalar valued UDF is a fancy name for a function that returns a single value, they can return any value that can be cast into a SQL Server datatype (int, bit, varchar, etc.), for this example we’ll return a simple string that will be cast as an nvarchar.  Not a very exciting example but you can see where you can use any C# code to do any complex processing to create the string that is returned.  Only thing unique here is the [SqlFunction] attribute which identifies that this static method defines a UDF.

using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
    public static SqlString ScalarUDF()
        return new SqlString ("Hello World!");

Example 2: Table-valued UDF

Another common type of UDF is a Table valued UDF which is simply a function that generates a table of data.  Obviously it goes without saying that returning a grid of data is more complex than a single value and as a result the code to accomplish it.

using System.Collections;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
    private class TableType
        public SqlInt32 Sequence { get; set; }
        public SqlString Text { get; set; }

    public void TableUDF_FillRow(
        object tableTypeObject, 
        out SqlInt32 sequence, 
        out SqlString text)
        var tableType = (TableType)tableTypeObject;

        sequence = tableType.Sequence;
        text = tableType.Text;

        DataAccess = DataAccessKind.Read, 
        TableDefinition = "Sequence int, Text nvarchar(100)", 
        FillRowMethodName = "TableUDF_FillRow")]
    public static IEnumerable TableUDF()
        var tableResults = new ArrayList
            new TableType {Sequence = 1, Text = "Hello"},
            new TableType {Sequence = 2, Text = "World"},
            new TableType {Sequence = 3, Text = "!"}

        return tableResults;

There are three key components of this one.

  • First create a private class that defines a Sql type for every “column” that will be in the table that you return
  • Second a FillRow method that SQL Server will use to take the returned IEnumerable into the various SQL column types that the table is made of one row at a time
  • Third the actual method where all of the UDF logic goes.  Take whatever action you want to create an IEnumerable of TableTypes that will make up the returned data

This is just a quick overview to get you started so you’ll need to check MSDN for details. 


Now that we have two bare bone UDFs lets compile them and deploy them to SQL Server.  After compiling the project and getting the DLL run the following from within SQL Server.

-- Enable CLR 
EXEC sp_configure 'show advanced options' , '1'
EXEC sp_configure 'clr enabled' , '1'
EXEC sp_configure 'show advanced options' , '0';
-- Install Assembly
-- Create ScalarUDF
RETURNS nvarchar(100)
EXTERNAL NAME UDF_Blog.UserDefinedFunctions.ScalarUDF;
-- Create TableUDF
RETURNS table 
    Sequence INT,
    Text NVARCHAR(100)
EXTERNAL NAME UDF_Blog.UserDefinedFunctions.TableUDF;

The comments really speak for themselves.  If this is the first CLR function that has been deployed to the server you need to enable that functionality, install the assembly that contains the compiled code, and install the functions one by one so they can be used.  When all of this is done you can execute them just like you would any other pure SQL UDF.

Closing Thoughts

Obviously these are very simplistic examples of what you can do but hopefully you can see the added flexibility that this option provides.  I will caution you to not overuse this, even though it is great to have this option it does add considerable complexity to your system.  if you can do in two lines of SQL what you are able to do in one line of C# it is not worth it, However for more complex things that you can do in maybe ten lines of C# compared with a hundred in SQL the trade off is more than worth it.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

JJ Bussert
United States United States
No Biography provided

You may also be interested in...


Comments and Discussions

QuestionHow do I add VB CLR Pin
Ritwik Dutta10-Dec-13 13:35
memberRitwik Dutta10-Dec-13 13:35 
QuestionNice simple example Pin
Brendan Costigan12-Nov-13 0:12
memberBrendan Costigan12-Nov-13 0:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.180111.1 | Last Updated 9 Nov 2013
Article Copyright 2013 by JJ Bussert
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid