BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
If you have a calculation that you frequently use in SQL queries you're probably bored of writing out the same code time after time. Why not try creating a user-defined function to save you the hassle? This blog teaches you how to define your own custom functions, including how to use input parameters, how to alter a function after you've created it, and even how to use fancy programming techniques such as variables and IF statements to help structure complex sequences of calculations.
- Scalar User-Defined Functions in SQL Server (this blog)
- Writing Custom Functions in SQL Server
- Creating and Using a Custom Function
- Altering and Deleting Custom Functions
- Writing Complex User-Defined Functions
Posted by Andrew Gould on 01 February 2013
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Scalar User-Defined Functions in SQL Server
In SQL Server, a scalar function is one which returns a single value, be that a string of text, a number, or a date. There are many built-in functions in SQL Server, but this blog will teach you how you can write your own user-defined functions, or UDFs.
Built-In SQL Server Functions
You may have used existing functions in your SQL queries in order to create calculated fields, such as in the example below:
--The length of the film name
--Replaces NULL with custom value
,ISNULL(FilmName, 'No title entered')
--The weekday of the date
--Date in UK format
--Converts one data type into another
,CAST(FilmRunTimeMinutes AS DECIMAL)/60
All of the functions used in the example above perform a useful role and there are many more built-in functions available:
Every SQL Server database has a Programmability folder giving you access to the built-in, system functions.
I Can't Find the Function I Need!
Although there are many system functions available, what happens if you can't find a function that meets your needs? In the query shown below we've used the built-in DATENAME function to create a custom format for the dates in a field:
,DATENAME(DW,FilmReleaseDate) + ' ' +
DATENAME(D,FilmReleaseDate) + ' ' +
DATENAME(M,FilmReleaseDate) + ' ' +
The four calls to the DATENAME function combine to create a single calculated column, as shown below:
The result is a neatly formatted column of dates.
Although the expression in the query works, it's a little long and unwieldy to be typing in every time I want to format dates in that particular way. Ideally, I'd like a function which encapsulates the logic of my expression which I can use in my queries in the same way as a built-in function. Unfortunately, no such function exists in SQL Server. The good news, however, is that you have the option to create your own custom functions! Read part 2 of this blog series to discover two ways to create a user-defined function.
An Important Note on Performance
Although scalar user-defined functions can be immensely useful, they can have a dramatic negative impact on query performance. Before you embark on a crusade to convert all of your inline expressions to custom functions it might be worth a quick Google of the phrase sql user defined function performance. Of course, if you're anything like me, after reading all the advice you'll create a UDF anyway and test its performance for yourself!