This page has 0 threads | Add post

Finding our blogs useful? Please consider helping us to share more!

Scalar User Defined Functions in SQL Server - UDFs Part one of a five-part series of blogs
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 This blog is part of our long and detailed online SQL tutorial. Another way to learn SQL is on one of our SQL training courses.

Scalar User Defined Functions in SQL Server - UDFs

Part one of a five-part series of blogs

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

This blog is part of our long and detailed online SQL tutorial. Another way to learn SQL is on one of our SQL training courses.

Posted by Andrew Gould on 01 February 2013

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:

SELECT

--The length of the film name

LEN(FilmName)

--Replaces NULL with custom value

,ISNULL(FilmName, 'No title entered')

--The weekday of the date

,DATENAME(DW,FilmReleaseDate)

--Date in UK format

,CONVERT(CHAR(10),FilmReleaseDate,103)

--Converts one data type into another

,CAST(FilmRunTimeMinutes AS DECIMAL)/60

FROM

tblFilm

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:

SELECT

FilmName

,DATENAME(DW,FilmReleaseDate) + ' ' +

DATENAME(D,FilmReleaseDate) + ' ' +

DATENAME(M,FilmReleaseDate) + ' ' +

DATENAME(YY,FilmReleaseDate)

FROM

tblFilm

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!

Parts of this blog
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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

Google Sheets

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC