In SQL Server, user-defined functions (UDFs) are similar to stored procedures, except that they do not support OUTPUT parameters. Instead, a user-defined function returns a value. The type of value returned depends on the type of function.

One difference between stored procedures and user-defined functions is that user-defined functions can be used in the SELECT statement, and you can join them to tables, views, CTE and even other functions. The second difference is that you can perform DML operations within stored procedures, but you cannot perform DML operations within user-defined functions.

As this webpage from MSDN says, in SQL Server (starting with 2016), you can create a database object inside an instance of SQL Server that is programmed in an assembly created in the Microsoft .NET Framework common language runtime (CLR). Database objects that can leverage the rich programming model provided by the common language runtime include aggregate functions, functions, stored procedures, triggers, and types.

Creating a CLR assembly is outside the scope of this article, however here are the steps involved:

1. Define the function as a static method of a class in a language supported by the .NET Framework.
2. Then, compile the class to build an assembly in the .NET Framework by using the appropriate language compiler.
3. Register the assembly in SQL Server by using the CREATE ASSEMBLY statement.
4. Create the function that references the registered assembly by using the CREATE FUNCTION statement.

As the book called SQL Server 2014 Development Essentials by Basit A. Masood-Al-Farooq, Copyright © 2014 Packt Publishing ISBN 978-1-78217-255-0 says: “In general, CLR user-defined functions are more suitable for computational tasks, string manipulation, and business logic, while Transact-SQL functions are more suitable for data-access-intensive logic.”

Creating a Scalar UDF

We use the CREATE FUNCTION statement to create a user-defined function. The CREATE FUNCTION syntax varies depending on the type of function you create.

For example, run the following Transact-SQL code to create the dbo.fnIsWeekday user-defined strong>scalar function within the AdventureWorks2012 database.

USE [AdventureWorks2012];
GO
CREATE FUNCTION dbo.fnIsWeekday ( @p_date [datetime] )
RETURNS [bit]
AS
	BEGIN
		DECLARE @weekday [bit]
		IF ( SELECT DATENAME(dw, @p_date)
			) IN ( N'Friday', N'Saturday', N'Sunday' )
			BEGIN
				SET @weekday = 0
			END
		ELSE
			BEGIN
				SET @weekday = 1
			END
		RETURN (@weekday)
	END;
GO

This user-defined scalar function accepts an input parameter (@p_date) and returns 1 if it is a weekend date and 0 if it is not a weekend date. Moreover, this function definition also includes control-of-flow statements. You need to provide a date to use this user-defined scalar function.

Using a Scalar UDF

You can use user-defined scalar function in the same way you use system scalar functions. For example, to use the above user-defined scalar function, run the following code in SSMS Query Editor:

USE [AdventureWorks2012];
GO
SELECT dbo.fnIsWeekday ('February 5, 2016');
GO