Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Earlier we saw a few examples of what are called aggregate functions in SQL, like using COUNT to return the total number of rows in a table or using MAX to return in the largest value in a particular column, or average or SUM or MIN. These functions, like in other languages, are just a way of packaging up a piece of useful behavior and giving it a name, so that we can use it again-and-again. When you're writing these in SQL Server Management Studio they tend to show up in hot pink.
We do have a few other aggregate functions, but these are really just the tip of the iceberg, because SQL Server has over 200 functions. But don't worry. You don't need to go and memorize every single one of them, because although some of these 200 things, like COUNT and MAX and MIN, are so common and so useful that you could end up using them on almost a daily basis, some functions are specialized enough that you might work with SQL Server for years and never come across them. But they're all packaged behavior, packaged operations.
So a simple and common operation like return the total number of rows becomes packaged up as the COUNT function and we can use it again-and-again. But we also have operations like "Return the angle in radians between the positive x-axis and the ray from the origin to the (y, x), where x and y are the values of the two specified float expressions." And that is also packaged up inside SQL as the ATN2 or arctangent function. But just between you and me, I've been working with SQL Server for over ten years and I've never needed this one.
So how do we get familiar with the good and the useful ones? I'm going to go through some of my favorite functions and show you how to use them. But like so many other things, treat Books Online as your friend and companion here. If you open up Books Online and go to the Index, you'll be able to look for the word Functions. Now when you do this, you're going to see them show up in many different areas. Functions for ODBC, SQL Server. There will be quite a few, depending on what you're filtering on. In fact the one that I'm interested in, is actually functions Transact-SQL, so functions for T-SQL, because it's a bit more specific and it gets down to the functions quicker.
And this page, I quite like this one, so I'm going to click somewhere in the page so that the Favorite button becomes active and I'll add that to my list of favorite, so I can come back to this whenever I need to. The functions are grouped. We have the Aggregate Functions here and I can drop-down and explore things like the Count function and see how it's meant to be used. Click back a couple of times, I've also got configuration functions, things like the server name and the version, mathematical function, signs and cosigns if I need those, security and string functions for working with the text.
And I can drill down into each of them and see examples and read about exactly how to use them. Now the thing about functions is they're actually stored as part of your database. In fact if I connect to my SQL Server instance and drill down into any of these databases, like AdventuresWorksLT, I'll find that each database has a Programmability folder and each of those has a Functions folder and each of those has a Systems Functions folder. And what I'm going to find here is a list just presented differently of the same available functions.
The aggregate functions that we have, the configuration, the date and time, the mathematical functions. And it can be useful to have these showing up in SQL Server just to take a quick look at what's available. Remind yourself what the name is for example. Now you'll see that most functions have the parentheses after them, and that's how they're called. We've seen examples using COUNT, passing in the star and parentheses, using average, passing in ListPrice, but you'll find even when you're not passing something in those parentheses, you still need them.
There is a very popular function called GETDATE, but when you use it you need the two parentheses even if it's empty. Now there are a few functions that just use the name of the function with no parentheses. If you're into geek trivia this is what's known as an Niladic function. Most of the functions without parentheses are easy to recognize because they have two @ signs in front of them. Although there are three or four little odd ones that don't like CURRENT_USER. It doesn't have parentheses and it doesn't have @ signs.
The two @ signs aren't magical. They're just a way of marking a few of these functions. In fact most of the ones with @ signs, if you see them in SQL Server Management Studio, you'll find them in the Configuration section and there are things like the version of the server. The server name, the language of the current server, the option set on it. The thing is you don't want to get worried about whether the two @ signs are there or whether the parentheses are there, because there is an easy answer. If you see the function in SQL Server Management Studio or in Books Online and it has parentheses, then you use parentheses. If it doesn't then you don't, end of story.
But the thing about all of these functions is that they give you a value back. COUNT gives me back an integer that represents the number of rows in that table. Average, well that gives me back whatever I passed into it. If ListPrice was a money column then I get a money average back. If it was an integer I get an integer back. If I used the GETDATE function I will get a date-time value back. If I used the @@LANGUAGE function I get a varchar back with, in this case US_English.
If I called the CURRENT_USER function, well depending on how my database is configured, I may get dbo. That will be a very common response from that. A few functions can return several results and that would usually be in the form of the table. But most functions are like these. They return what's called a scalar value. This is an old computing term for a single value. COUNT is a function that returns one integer. GETDATE returns one date time value. They don't return a collection, they don't return an array. They return one scalar value.
So how do you use these functions? Well we've seen functions used in SELECt statements and that's very, very common but they can be used in WHERE clauses, in UPDATEs, INSERTs, all over the place. So next I'll take you through a few of them.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98804 Viewers
80 Video lessons · 141651 Viewers
59 Video lessons · 59999 Viewers
52 Video lessons · 73169 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.