Enhancing SQL Server Functionality with Functions
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
In this new series of articles on functions, I will discuss built-in and user-defined functions in SQL Server 2000. Built-in functions provide a way to check system information and metadata or enhance the functionality of your code by manipulating the data in system and user databases. Built-in functions have been around since the beginnings of SQL Server. On the other hand, user-defined functions (UDF), which are new in SQL Server 2000, allow you to encapsulate the logic you need to reuse in an efficient code module.
There are numerous built-in functions in SQL Server, and I won't discuss each one of them in great detail. Instead, I will introduce you to various types of built-in functions and discuss the ones I have found most useful, with limited coverage of the ones that can be used only in specific circumstances.
In this first article, I will talk about various types of built-in functions and focus on aggregate functions. The following articles will go into details of both built-in functions and UDFs.
Function Determinism
Before we dive into studying the intricacies of each built-in function type, you need to be familiar with some nomenclature. The terms we introduce here might not seem too relevant yet, but they will help when you try to use a certain function in some code module and it doesn't work.
Deterministic functions always return the same value for the given set of parameters. Let's examine the deterministic function ISNUMERIC. This function accepts a single parameter and returns a Boolean value; if the parameter is of numeric data type (integer, decimal, float, and so on), 1 is returned; otherwise, 0 is returned. ISNUMERIC is a deterministic function because it always returns 0 for any string passed in, regardless of system load, the user who executed the function, time of day, or any other condition. For instance, the following always returns 0:
SELECT ISNUMERIC('InformIT.com')
Similarly, if you pass in a numeric expression, whether it has decimal points or not, ISNUMERIC always returns 1, as in the following:
SELECT ISNUMERIC('123.349024845')
Non-deterministic functions return different results for the same set of parameters based on who executed the function, time of day, or other circumstances. For instance, the SYSTEM_USER function is non-deterministic; this function does not take in any parameters—it simply returns the current user name. So if I'm logged on as "JohnDoe", executing SYSTEM_USER returns "JohnDoe"; whereas if I'm logged on as "sa", SYSTEM_USER returns "sa".
The large majority of functions can be classified as deterministic or non-deterministic. A few, however, can be either deterministic or not, depending on the data type of the parameters. For example, the CAST function translates a parameter from one data type to another. Although CAST is deterministic in most cases, if it is used with the DATETIME/SMALLDATETIME data type, it is non-deterministic. SQL Server supports various formats for date values, so the value returned from CAST when used with DATETIME depends on the DATEFORMAT setting. For example, the following two statements CAST the same value as SMALLDATETIME, but return different results:
SET DATEFORMAT mdy
SELECT CAST('10/1/02' AS SMALLDATETIME)
Results:
2002-10-01 00:00:00
SET DATEFORMAT dmy
SELECT CAST('10/1/02' AS SMALLDATETIME)
Results:
2002-01-10 00:00:00
So why do we care whether a built-in function is deterministic or not? There are certain limitations associated with non-deterministic functions. Perhaps the most important limitation is that you cannot call a non-deterministic function within a user-defined function. So you can't use GETDATE(), NEWID(), RAND() functions in UDFs, even though you can use them in stored procedures.
Other limitations include the fact that you cannot create an index on a computed column that references a non-deterministic function. Nor can you create a clustered index on a view that references a non-deterministic function.