This short article explains the steps necessary to create and deploy a C# Library to SQL Server to use the faster execution of Scalar Functions, e.g. to convert any value to DateTime.

TODO: Describe C# Project and Code

Project Build Steps

Activate the “Optimize Code” and “Delay Sign Only” checkmarks in the Properties of the Project and Compile the Project.

Deployment of Lib to SQL Server

Copy the compiled Lib.dll to a Location on the SQL Server, e.g. C:\DWH\Lib.dll
The SQL Server Database Engine User (see Services) requires Read & Execute Permissions for the File Location.

Execute Script in SSMS to Register Assembly

sp_configure 'clr strict security', 0;  
GO  
RECONFIGURE;  
GO  
USE DWH;
GO

-- DLL neu registrieren...
CREATE ASSEMBLY ClrFunctions from 'C:\DWH\SqlServerClrFunctionsLib.dll' WITH PERMISSION_SET = SAFE;

Register a new Scalar Function

CREATE FUNCTION common.ToDateTime(@value sql_variant,
	@valueIfNull datetime2(7),
	@valueIfConvertError datetime2(7)) RETURNS datetime2(7)
	EXTERNAL NAME ClrFunctions.Scalar.ToDateTime;
GO

Some Scripts for Maintanence

-- When code has been changed within DLL:
	ALTER ASSEMBLY ClrFunctions from 'C:\DWH\SqlServerClrFunctionsLib.dll' WITH PERMISSION_SET = SAFE , UNCHECKED DATA;
-- ^^ it's possible that this step does not succeed. A restart of the Database Engine Service may be required.

-- To Change the Signature of a Function, first drop then re-create function...
BEGIN TRAN

DROP FUNCTION common.ToBit;
GO
CREATE FUNCTION common.ToBit(@value sql_variant,
	@valueIfNull bit,
	@valueIfConvertError bit) RETURNS bit
	EXTERNAL NAME ClrFunctions.Scalar.ToBit;
GO

COMMIT

More Information to Strong Names and its purpose

https://www.codeproject.com/articles/8874/strong-names-explained