Overview

Introduction

The Implementing Functions module provides you with the instructions and devices to develop your hands-on skills in the following topics.

  • Working with scalar functions
  • Using table-valued functions in Transact-SQL
  • Demonstrating the impact of using functions with WHERE clause on query performance

Lab time: It will take approximately 45 minutes to complete this lab.

Exam Objectives

The following exam objectives are covered in this lab:

  • Demonstrate how to create and implement multi-statement scalar function with input parameters by using Transact-SQL. Write and execute a query that invokes the scalar function
  • Demonstrate how to create and implement a table-valued function with input parameters by using Transact-SQL. Write and execute a query that invokes the table-valued function
  • Demonstrate how the usage of functions in the Where clause impact query performance

Exercise 1 - Working with Scalar Functions

A function is a database object that has a set of SQL statements. It accepts a parameter, performs an action and returns the result. Functions can be used in constraints, computed columns, JOIN, WHERE clauses and so on. The result can consist of a single value or a table depending on the type of function. Functions can be scalar or table-valued that returns a single value or table respectively.

The scalar function returns a single value of the data type specified in the RETURNS clause. You can use scalar functions when you want the function to perform calculations on the fields in the table and return a single value. For example, you can use the scalar function if you want to calculate the average sales in a week, the total quantity of a product sold in a month and so on.

In this exercise, you will learn to create and implement scalar functions.

Exercise 2 - Using Table-Valued Functions in Transact-SQL

Table-Valued Function (TVF) returns an entire table as a result. The table-valued function statement has three parts, which are as follows:

  • RETURNS clause: It defines the local return variable to retrieve the table returned by the function.
  • Function body: It inserts records in the return variable as per the condition specified.
  • RETURN statement: It returns the resultant table as the output of the function. TVFs can be reused in query statements.

There are two types of table-valued functions such as:

  • Inline TVFs

    1.The syntax of an inline TVF is as follows:

  • Multi-Statement TVFs

In this exercise, you will learn to create and implement both the types of table-valued functions.

Exercise 3 - Demonstrating the Impact of Using Functions with WHERE clause on Query Performance

You can graphically represent the query performance by generating the execution plans of the query. The execution plan displays the plan followed by the SQL Database engine to execute a query.

Using functions with WHERE clause may slow down the query execution process and increase the estimated operator cost to execute such query. The execution plan also shows the resources utilized to execute the query.

In this exercise, you will learn to demonstrate how the use of functions in WHERE clause affects the query performance.

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.