From the course: Complete Guide to Advanced SQL Server

Deterministic vs. nondeterministic functions - SQL Server Tutorial

From the course: Complete Guide to Advanced SQL Server

Start my 1-month free trial Buy for my team

Deterministic vs. nondeterministic functions

“

SQL Server ships with a ton of built-in functions that are useful for exploring and analyzing your data. Like in other programming languages, SQL Server's functions usually take a set of input parameters or arguments and then run those values through their programming logic and return a result. Some functions don't take any arguments, though. For instance, the GETDATE function or the RAND function don't have any arguments. You still need to include the open and closed parentheses, though, when you execute them. GETDATE will return the current system date and time, and RAND returns a random number. In order to see the results, I'm going to put them into a SELECT statement. So we'll SELECT GETDATE As Today, and SELECT RAND As Random. This will return two result windows, and I can see that the GETDATE function is returning the current system date and time on my computer, and the random function is simply returning a random number between zero and one. Now, most functions do require at least one argument. If I query out the ColdRoomTemperatures_Archives table from the warehouse schema now to select the top 1000 rows, you'll see that this table includes a column for the temperatures that are recorded. Now, we're just taking a look at the first 1000 rows from this table, but there are actually over a million different records that we could take a look at. If you just wanted to find the highest, lowest, and the average temperature recorded, you can use the max, min, or average functions against the temperature column from the table. Let's go ahead and run this SELECT statement on Line 11 through 15 to select the maximum temperature, minimum temperature, and average temperature from Warehouse.ColdRoomTemperatures_Archive. These aggregate calculations will quickly give us an idea of the scale of the data. We can see that the highest temperature recorded is five degrees, the lowest temperature is three degrees, with an average of 3.99 degrees. Other functions will take multiple arguments for processing. On Line Number 18, we're going to take a look at the function called FORMAT. The FORMAT function can be used to return a date as a text string. Here, we're going to format the dates that are stored in the InvoiceDate column of the Sales.Invoices table. That provides the first argument for the FORMAT function. This function also requires a second argument. And I can see that here after the comma, and the second argument, I'm just specifying a lowercase d. The lowercase d tells the function that I want the date to be formatted to the standard for my server's region. For me, that's the month number slash day slash year. If you're working in a different region, it's possible that you'll see a different result when you execute this function. Here, I can see the original invoice dates that are stored in the Wide World Importers database. And here is the result of the formatted date after it's run through the format function. Now, you can also nest functions inside of other functions. On Line Number 24, I'm doing that again with the FORMAT function. But instead of processing data from the database, I'm going to process the current system date and time that's coming out of the GETDATE function. This time, for the second parameter, I'm supplying a pattern to format the dates with so that everyone that runs this function will see the same result. Let's go ahead and highlight this and execute it. And here, I can see the result. It's formatting the date with the name of the month, the two-digit day, the four-digit year, and then we have the time after that. Now, as I said, there are lots of different built-in functions that are useful for analyzing and manipulating your data, and you owe it to yourself to explore their capabilities. The documentation at this page is a good place to start to explore the different categories of available functions. Over here on the left-hand sidebar, we have links for aggregate functions, analytic functions, correlation functions, and all of these down the list. But these are just the start. SQL Server allows developers to create their own functions that are custom-written to work with their own unique sets of requirements. Before we take a look at that, though, we need to talk about function determinism. Deterministic functions always return the same value for the same set of inputs. Now, given this definition, can you go through the functions that we just looked at and figure out which ones are deterministic and which ones are non-deterministic? Well, the max, min, and average functions will return the same value every time I run this query. When I execute this SELECT statement again, we get the same values of 5, 3, and 3.99 again. The GETDATE and RAND functions, on the other hand, that we have up here at the top, return different values every time I execute the statement. This time, you can see that the time is a little bit later than it was a moment ago, and the random number is completely different. This is even, though I'm executing them in exactly the same way. So these two are non-deterministic functions. The FORMAT function is also non-deterministic. If you remember, I mentioned that this parameter, the lowercase d, in this case, returns a value depending on the cultural region of the server, and it can change depending on where the code is executed. Even if you process the same values specified in the first parameter. Now, as we saw in the last chapter, non-deterministic functions can prevent us from doing certain things in the database. You cannot create an indexed view, for example, if the view definition includes a non-deterministic function. Functions that call stored procedures are also non-deterministic, since the stored procedure has the ability to make changes to the data while the function is executing. So the determinism of your functions is something to keep in mind when adding programmability objects to your database's design. Just remember that there may be side effects down the line if you incorporate non-deterministic functions into your system.

Contents