Businesses are running behind applications, tools, and software that makes their lives easier by reducing runtime, accelerating productivity hours, and decreasing man-hours. It is imperative for businesses to maintain large databases yet make it simpler to fetch and utilize the database. In SQL Server, UDF User-defined function is a programming language that accepts multiple parameters and utilizes the same approved parameters to deliver a specific outcome.

SQL Server also supports CLR functions, which can be written in any Microsoft .Net programming language like C# or VB.Net.

1. CLR User-Defined function

Following are steps to create a new CLR User Designed function.

  1. To start with, Create a new SQL-Server Database Project in Visual Studio.
  2. Right Click on the Project button and select Add->New Item->SQL CLR C#.

    Add SQL CLR C User Defined Function
    Image 1 Add SQL CLR C# User Defined Function
  3. It will then open a New File for writing function in C#.
    Write SQL Function in C#
    Write SQL Function in C#

    We can write C# code here. We can also add third-party dll and use it in C# code.

  4. After writing the function, from the solution explorer, right-click on the project. Select the Publish option.
    Publish Database
    Publish Database

    Now select the target database, then click on Publish or Generate Script option.

  5. This Function will create the database and is easily seen in the Management Studio.

    Function in Management Studio
    Function in Management Studio
  6. The SQL Function has no difference and is similar to SQL statement that users execute normally

    Function Execution
    Function Execution

2. Time Zone and Daylight-Saving Time

SYSDATETIMEOFFSET() Function returns current local date, time & time zone offset value. Time zone offset values is represented as [+|-] hh:mm (E.g. for India, Its +05:30). However, from this value, the time zone name is not identified. For example, the time zones of India and Sri Lanka have the same offset value. Similarly, using this function, one cannot identify that this is Daylight Saving Time (DST) or not.

To get these values, the following CLR functions can be helpful.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
/// Check current time is in Daylight Saving time
/// If current time zone is daylight saving, then return true else false
public static SqlBoolean IsDayLightSavingTime()
return TimeZone.CurrentTimeZone.IsDaylightSavingTime(DateTime.Now);
/// Get Current Time Zone Name
/// Time Zone Name
public static SqlString TimeZoneName()
//If current time is daylight saving , then return day light name , else it will return Standard Name of time zone.
if (TimeZone.CurrentTimeZone.IsDaylightSavingTime(DateTime.Now))
return TimeZone.CurrentTimeZone.DaylightName;
return TimeZone.CurrentTimeZone.StandardName;


Notes: CLR function might not be available in SQL Server in default options.

Software development companies can enable change by executing the following scripts. However, this command is not recommended. To allow CLR, developers should use the Asymmetric key or certificate.

EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'clr strict security', 0;
EXEC sp_configure 'clr enabled', 1;

3. Conclusion

With this blog, Now it would be easy to fetch and analyze data using SQL in Visual Studio. A comprehensive understanding of how queries work, how to use user-defined functions, using time zone functions to automatically generate time. As explained in the above example of CLR function, users can now create user-defined functions in C# or VB.Net and take advantage of .net development services and third-party DLLs.

Vishal Shah

Vishal Shah has an extensive understanding of multiple application development frameworks and holds an upper hand with newer trends in order to strive and thrive in the dynamic market. He has nurtured his managerial growth in both technical and business aspects and gives his expertise through his blog posts.

Related Service

Know more about our .Net Development Services

Learn More

Want to Hire Skilled Developers


    • Leave a message...

    Related Articles
    Asp net Core and Docker
    ASP.NET Core and Docker
    Jul 2, 2021
    Amazon Lambda vs Azure Functions
    AWS Lambda vs Azure Functions: Serverless Computing
    Mar 22, 2021
    Optimize SQL
    Optimize SQL Query using Execution Plan for High Performance
    Feb 4, 2021