98-364 MTA Microsoft Database Fundamentals – Creating Database Objects Part 2

3. Functions

A database function is a transact-SQL routine that accepts parameters, performs an action, and returns the result of that action as a value. In SQL Server, we have numerous built-in functions at our disposal. These functions are referred to as “system-defined functions.” Depending on the behavior of the functions, we can further categories them into scalar functions and aggregate functions. Scalar functions are functions that can accept one or more parameters and always return a single value. Aggregate functions, which we’ve already covered, are pretty similar to scalar functions. These types of functions operate on a set of values and return a single value.

So these are the two system-defined functions you should be familiar with. But these are not the only types of functions that are built into SQL Server. We also have analytical functions, ranking functions, and so on. Aside from the built-in functions, we can also define our own functions. These functions are referred to as “user-defined functions.” Here we have scalar functions, inline table-valued functions, and multistatement table-valued functions. These are all the types of functions we can create in SQL Server. We’ve already covered scalar functions, so let’s move on to table-valued functions.

Table-valued functions, as their name suggests, are functions that return a table as a value. The inline table valued function is pretty similar to a view; the biggest difference is that it can accept one or more parameters. So with a view, we have a static query, which means that every time we query the view, the same thing will happen. With the inline table-valued function, we can be a bit more dynamic. We can, for example, retrieve data or perhaps perform computations based on the parameters passed to the function. And then we have the multistate table-valued function. As the name suggests, we can define multiple queries within this function. Another important characteristic of this type of function is the fact that we’re explicitly defining the structure of the table we’re returning. So we could, for example, create a number of queries that retrieve data from different tables, perform different computations on the data, and have the table we’ve defined populated with the result of all these queries.

This way, we’re returning the result of all the statements as if it were a single table. For this exam, we’re only going to look at COVID aggregate and scalar functions. So let’s head over to SQL Server Management Studio to create our first database function. We’re first going to look at some of the built-in functions we have in SQL Server. So let’s go to the Object Explorer, expand the Programmability Node, and expand the Functions Node as well. Here you’ll see a classification of functions that are within this database. For now, we’re just going to expand the System Functions node. Here we have another classification of functions. If we take a look at the aggregate functions, you’ll see some familiar functions we’ve already used, such as the average function, the count function, the max function, the min function, and the sum function. Hovering over a function gives a brief description of what the function does. If we double-click on a function and expand the parameters node, we’ll see the type of data the function accepts as parameters and the type of data it returns. So this function accepts an expression that results in a set of numerical values and returns a single numerical value. Let’s look at a few scalar functions, such as date and time functions.

Here we have a number of built-in functions that allow us to easily manipulate temporal data. For example, the year function If we expand it and also the parameters node, you’ll see that this function accepts an expression that evaluates to a date time value and returns a single integer, which is basically the year component of the date time value. Let’s open a new query window to use this function. So to invoke this function, we can simply write select. Then we specify the name of the function. Here you’ll also be able to see what types of data this function is expecting.

Let’s write a date, a time value, and finally the semicolon. If we execute this query, we should see only the year value returned, which is 1998. This is an example of an scalar function that accepts a parameter. Let’s look at a function that does not accept parameters. For example, the Get Date function This function returns the current system date and time. If we double-click on it and expand the parameters node, you’ll see that it accepts no parameters but returns a single date and time value. So to invoke this function, we simply right-select followed by the name of the function. We’ll leave the parentheses and semicolon empty because it doesn’t accept any parameters. If we execute this query, we get the current system data and time value. So there are a number of different built-in functions at our disposal.

These functions can be very helpful in the life of a database developer or administrator, so I’ll advise you to play around with them in your free time. We are now going to create our first user-defined scalar function. This function will be a parameter less function named “get,” my lucky number, and will return a single integer value of nine. So to create the function, start with the create command. We then specify the type of object we want to create, which is a function followed by the name of the object. As a naming convention, we usually start the names of functions with the prefix FN and then the name of the function. Next, we specify the parameter list by using parentheses. Since we’re not going to have any parameters in this function, we’ll leave these parentheses empty. Next, we specify the type of data this function will return.

We do that by writing returns followed by the data type. This function will return an integer value. So we simply write int. We then use the as clause followed by the begin command, which initiates the block of code that this function will execute. We then end the block of code by writing the end command. This part of the function is called the signature or header, while this part is called the body of the function and is basically where the logic lies. As I’ve stated during the slides, functions basically always return a value, which means we need to have a return statement.

Since this function will do nothing but return the arithmetical value of nine, we simply write “return nine.” If we execute this DDL statement, we get a message stating that the command was completed successfully. If we now head over to the Object Explorer and expand the Scalar Functions node, you’d be able to see our newly created function. We are now going to write a query to invoke the function. First, we start with a select command, and then we specify the name of the function. If we execute this query, we should get a result of nine. This is the logic we have written in the body of the function.

Let’s take a look at a more practical example. Here we have a select statement that retrieves the number of songs we have for each artist. We’re going to make a parameterized function of this query. The function is going to accept one parameter, which is going to be the stage name, and we’ll return the number of songs we have for the artist with the associated stage name. So first we write the create command, followed by the type of object we’re creating, and then the name of the object. We’re going to name this function. Get the total number of songs by stage name. Now we need to define our parameter list. This function will accept only one parameter that will be compared with the stage name column. A parameter is basically a variable and can be seen as a container that stores the data the user sends to the function. When we declare a variable using transact SQL, we use the at symbol followed by the name of the variable.

We’re going to call this variable stage “Name.” Then we use the as clause, after which we specify the data type for this variable. Since we’re going to compare the value of this variable with the stage name column, we’re going to give it the same data type, which is varchar with a maximum size of 25. We are now done with the parameter list. This is the part where we specify the type of data that will be returned. This function will return an integer value, so we simply write “Returns.” Here we use the as clause again. Then we write the begin command, and at the end of the select statement, we write the end command to signify the end of the function’s definition. So we want this function to return the total number of songs we have stored for a specific artist. This means we need to filter the data. To do that, we’re going to use the where clause. The where clause will filter the data so we can get only the result where the stage name column is equal to the value of the stage name parameter. Now there is something very important we need to do. We need to write the return statement.

Since this is not a table-valued function, we cannot return a select statement. We can only return a single value. So to capture the count result from this select statement, we’re going to declare a variable within the body of the function. This variable will store the total number of songs retrieved from the select statement. Once we store the total number of songs in this variable, we’re going to return its value. So to declare a variable, we simply write “declare.” Then we specify the name of the variable, followed by the ascii, and finally the data type. Now we can write the return statement, but we’re not done yet. We need to store the result of the query in the total songs variable. To do that, we need to refactor the select statement. First, we’re going to remove the alias and the stagename column, since they no longer have any purpose. In the select list of this select statement, we’re going to assign the value of the count function to the total songs variable. The DDL statement to create our function is now complete.

Let’s execute it to see the result. The function is now created. We can see our newly created function if we go to the Object Explorer and refresh the Scalar Valued functions. We can now go ahead and select this function, bypassing the stage name of an artist as a parameter. Let’s say we want to get the total number of songs we have for Usher. If we execute this query, we get a result of five. That’s because we have data stored for five songs made by Usher. Here is a more practical usage of the function. This query gets the total number of songs for each artist. As a result, the first select statement contained two joins. Because we were retrieving data from three tables, we then created a parameterized function to get the total number of songs based on the stage name of an artist. So whenever we need to create a query that, among other things, gets the total number of songs by an artist, we no longer have to join three tables.

That process is now automated by using this function. If we execute this, we should get the same result as when we executed the initial select statement, with the exception of the nulls. To include the nulls, we would need to perform additional logic. But I’m not going to elaborate on that in this video. We are now going to look at altering a function. When we alter a function, we also need the function’s definition to be present so we can modify the function’s logic. In the previous lecture, I’ve attached a document that covers a few methods you can use to get the definition of database objects. Since we have the definition right here, we can simply replace the create command with the alter command. We’re going to modify the return statement so that we can return the total songs multiplied by ten just for demonstrating purposes. If we execute the select statement after we execute this DDL statement, our function should be successfully altered. And again, we should see the total number of songs multiplied by ten. We’ve now covered the basics of functions. In the next video, we’re going to do a brief introduction about triggers. Thank you for watching. see you then.

4. Triggers

A database trigger is a procedure that is automatically executed in response to certain events within the database. These events could, for example, be a DML or a DDL operation. The most common reason triggers are used is to perform complex integrity checks for enforcing business rules in cases where the check constraint cannot be used. Say we, for example, have a products table in which we store lots of data about the products we sell, including the prices of the products. When modifying the data within this table, the prices of the products should not be allowed to go down.

They either stay the same or are raised. This means that when data changes occur on the products table, we would need to compare the current price with the new price. If the new price is less than the current price, such an operation needs to fail. This cannot be done with a check constraint, so we use triggers to achieve the desired result. Let’s look at another example. Say we have a table wherein we store data about the courses students enrol in. One of the rules is that each student can enrol in only three courses at a time. Once one of these courses has been completed, the student can enrol in another course. This means that every time a new record is created in this table, we will need to check how many active courses the student is enrolled in. If the student already has three active courses, we don’t want this operation to complete. Since the check constraint cannot perform such a check, we use triggers to enforce this business rule. So this is basically one of the biggest reasons why you would find a need for triggers.

Another reason why we would find ourselves using triggers is when designing the database in a manner to facilitate history and an audit trail. Although both history and the audit trail are related concepts, they are not the same. But both essentially refer to the process of keeping track of the data modifications performed by database users as well as being able to see the database as it existed at any previous point in time. When we update data in a table, the old data will be overwritten with the new data. This means that we no longer have access to the old data because it no longer exists. Sometimes this is exactly what you want. However, most of the time, this is not what you want. Say we, for example, have an employee’s table that stores data about each employee, including their salary. We have an employee who joined the company somewhere. In 2016. This employee had a starting salary of $4,000. Each year, the salary was increased by one K.

Now in 2020, if I query the database to get the data as it existed in 2016, I should see a salary of 4K. Subsequently, if I query the database as it existed in 2018, I should see a salary of 6K, and if I query the database as it exists today, I should see a salary of 8K. Facilitating such capabilities is mostly done through the use of database triggers. Data changes are then either written to a different table, usually referred to as a history table, or implemented in such a way that they remain in the same table. These are just the two main reasons why we would want to use triggers. As we stated earlier, triggers are triggered by an event in the database. Depending on the types of events that can occur in the database, we have the following types of triggers: First, we have the DDL triggers. These types of triggers are automatically executed when we perform DDL operations such as creating, altering, or dropping database objects. Secondly, we have DML triggers. These types of triggers are automatically executed when we perform DML operations such as inserting, modifying, or deleting data. These types of triggers are the most commonly used triggers.

Another cool trick about these types of triggers is that we can decide when we want them to execute. We have “after” triggers, which will execute after a DML operation has taken place, and we have “instead of” triggers, which are executed instead of the DML operation. Here, we can either do something before the DML operation takes place, such as integrity checks before modifying the data, or something completely different from the DML operation performed. Then we have CLR triggers, which are triggers written using CLR languages like C#, F#, and Visual Basic. And finally, we have logon triggers. When the SQL Server login event is raised, logon triggers are automatically executed. This event is raised when a user session is being established with SQL Server. We can use these triggers to audit and control server sessions, such as to track login activity or limit the number of sessions for a specific login. Triggers are actually not required for the exam, but it’s good to be familiar with these types of objects. That’s it for triggers. Thank you for watching. In the next video, we’re going to discuss COVID stored procedures. see you then.