SQL User Defined Function (UDF) in NoSQL Databases

UDF[1] (User Defined Function) have become an integral part of data retrieval languages and database systems. Just like function in programming languages, UDF are used to create reusable and programmable routines. UDF accept parameters from within a SQL query, perform complex computation and return a result. CLR UDF in a NoSQL Database allows you to customize your SQL queries in a way not possible until now, because of the fact that you can write the UDF logic in .NET. This opens up a whole new way of freely expressing your complex computation in a language with which you are completely familiar.

Running .NET code inside an SQL query is great but you must be wondering where exactly can these UDFs be used for maximum advantage. Following are a couple of simple examples answering this question.

RegEx in NoSQL

RegEx[2], which stands for Regular Expression, is a special text string which is used for the description of a particular search pattern. For example, the following regex is used to find an email address:

When it comes to performing RegEx evaluations in the WHERE clause while querying data, UDFs are a way better option. For example, CLR UDF written in .NET is as follows:

And the query is as follows:

Custom Aggregates

Most query languages provide built in scalar functions like SUM, MIN, MAX, COUNT, AVG and more. UDFs allow you to complete statistical operations that are not doable in the built-in scalar functions. For example, a Median. There is no support of Median in built-in scalar functions but the developer can design code where it takes an array of values and returns the median against that document. For example:

The following declared UDF can be used within an SQL query in the following way:

Another big plus in UDFs is that you can pass multiple parameters, making it easy for the user to contribute more information in the function. The user can pass any type of data to the UDF but eventually must cater for that data type otherwise an exception can be thrown hence it will fail to evaluate and your query process will die.

Other UDF Examples

  • Generate a GUID: Normally there are no built-in functions like GUID() so writing a UDF that generates a simple GUID comes in quite handy when inserting data using queries.
  • Generate a random number: As mentioned above RAND() is not a built-in function so writing a function that generates a random number depending on the logic written by the programmer is quite useful.
  • Query within UDFs: UDFs give you the ability to connect to another database and run a particular query on that database.
  • Nested UDFs: You can use a UDF with-in another UDF. For Example:

Advantages of UDFs at Server Side

Network Usage

One of the most basic advantages of UDFs[3] is saving network bandwidth. Any operation which uses some filtration before sending the result to the client, or that generates scalar results, means saving excessive costs. UDFs do this by performing calculations near the dataset.


UDFs is one of the major features that helps us achieve the objective of server side processing and in a distributed manner. Also since UDF in NosDB are written in .NET therefore there is no inter-process communication.

With UDFs in .NET, also comes the option to use threading. You could also incorporate CUDA in your calculations if your requirement is math intensive.

Error Handling

In query languages, it is impossible to handle errors as none of them provide support features like TRY/CATCH and in-case something goes wrong during execution of the query the whole query fails. Contrary to that UDFs, since they are written in .NET, provide full support for error handling and logging. You can return error messages using string for your convenience.

To conclude, these are just some of the main advantages of CLR UDFs because technically you can write any sort of code that you would typically write in .NET but one must be careful of the consequences and results as the code will run within your NoSQL database servers.



[1] https://en.wikipedia.org/wiki/User-defined_function
[2] http://emailregex.com/
[3] https://msdn.microsoft.com/en-us/library/ms191007.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *