0
Using Built-In Scalar Functions
Posted by Danielle Smith
on
15:00
in
Scalar Functions
Hi everyone! I hope all of my UK bloggers had a lovely bank holiday weekend!
Today's blog post is going to cover some of the more common SQL Built-in Scalar Functions, as you may come across them and find that you will have to use them in some of your projects.
Working similarly to aggregate functions (see my Implementing Aggregates blog post), scalar functions return a single value, however it is calculated on the input value. Typically, scalar functions should be used in the SELECT part of the statement for performance, but can also be used within the WHERE clause as well.
To use built-in functions, they are declared as:
Quite often, you will see that the brackets (or parentheses) will be empty. This indicates to SQL Server that it should take the current value when performing the function. When there is no parameter, this is called a non-deterministic function. When parameters have been defined, this is called a deterministic function. Due to the nature of SQL built-in scalar functions, the majority accept no parameters and are therefore non-deterministic.
Scalar functions are very useful, particularly when populating data and constraints. Once I have learnt more about their application, I will expand and refer back to this blog post.
Today's blog post is going to cover some of the more common SQL Built-in Scalar Functions, as you may come across them and find that you will have to use them in some of your projects.
So What Are Scalar Functions?
Working similarly to aggregate functions (see my Implementing Aggregates blog post), scalar functions return a single value, however it is calculated on the input value. Typically, scalar functions should be used in the SELECT part of the statement for performance, but can also be used within the WHERE clause as well.
To use built-in functions, they are declared as:
Quite often, you will see that the brackets (or parentheses) will be empty. This indicates to SQL Server that it should take the current value when performing the function. When there is no parameter, this is called a non-deterministic function. When parameters have been defined, this is called a deterministic function. Due to the nature of SQL built-in scalar functions, the majority accept no parameters and are therefore non-deterministic.
Examples of Built-In Functions
Below are some tables just demonstrating some of the more common functions that I have come across during projects in the working world (Obviously there are many more but these I have found are the most commonly used).
Date and Time Functions
Date and Time Functions are manipulations to DateTime inputs:
String Functions
String Functions are manipulations to string inputs:
CAST and CONVERT Functions
Scalar functions are very useful, particularly when populating data and constraints. Once I have learnt more about their application, I will expand and refer back to this blog post.
Post a Comment
Please post any feedback or comments here...