User-defined functions in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a scalar user-defined function

Consider the following SQL user-defined function and query:

CREATE FUNCTION dbo.MultiplyByTwo(@num INT) RETURNS INT AS BEGIN RETURN @num * 2; END;
SELECT dbo.MultiplyByTwo(5) AS Result;

What is the output of the SELECT query?

SQL

CREATE FUNCTION dbo.MultiplyByTwo(@num INT) RETURNS INT AS BEGIN RETURN @num * 2; END;
SELECT dbo.MultiplyByTwo(5) AS Result;

A Result: 10

B Result: 7

C Result: 25

D Syntax error in function definition

💡 Hint

The function multiplies the input by 2.

The function takes an integer input and returns its value multiplied by 2. So 5 * 2 = 10.

📝 Syntax

intermediate

Identify the syntax error in this table-valued function

Examine the following SQL code for a table-valued function:

CREATE FUNCTION dbo.GetEvenNumbers() RETURNS TABLE AS RETURN (SELECT Number FROM Numbers WHERE Number % 2 = 0);

Which option correctly identifies the syntax error?

SQL

CREATE FUNCTION dbo.GetEvenNumbers() RETURNS TABLE AS RETURN (SELECT Number FROM Numbers WHERE Number % 2 = 0);

A Missing BEGIN and END block around the RETURN statement

B Incorrect use of RETURNS TABLE without specifying TABLE type

C No syntax error; the function is valid

D Missing parentheses after RETURNS TABLE

💡 Hint

Inline table-valued functions can omit BEGIN and END.

This is a valid inline table-valued function syntax in SQL Server. It returns a table directly from the SELECT statement.

❓ optimization

advanced

Optimizing a scalar function for performance

You have a scalar function that calculates the square of a number:

CREATE FUNCTION dbo.Square(@x INT) RETURNS INT AS BEGIN RETURN @x * @x; END;

Which option is the best way to optimize this function for performance in SQL Server?

SQL

CREATE FUNCTION dbo.Square(@x INT) RETURNS INT AS BEGIN RETURN @x * @x; END;

A Keep it as a scalar function but add WITH SCHEMABINDING

B Rewrite it as an inline scalar function using RETURNS without BEGIN...END

C Rewrite it as an inline table-valued function

D Convert it to a stored procedure

💡 Hint

Inline scalar functions can improve performance by avoiding context switches.

Using an inline scalar function (RETURNS expression without BEGIN...END) allows SQL Server to inline the function logic, improving performance.

🔧 Debug

advanced

Debugging a user-defined function with NULL handling

Given this function:

CREATE FUNCTION dbo.SafeDivide(@a INT, @b INT) RETURNS FLOAT AS BEGIN RETURN @a / @b; END;

What error or issue will occur when calling dbo.SafeDivide(10, 0)?

SQL

CREATE FUNCTION dbo.SafeDivide(@a INT, @b INT) RETURNS FLOAT AS BEGIN RETURN @a / @b; END;

A Division by zero error at runtime

B Returns NULL without error

C Syntax error in function definition

D Returns 0 without error

💡 Hint

Dividing by zero is not allowed in SQL arithmetic.

Dividing by zero causes a runtime error in SQL Server, so calling the function with 0 as denominator will fail.

🧠 Conceptual

expert

Understanding side effects in user-defined functions

Which statement about user-defined functions (UDFs) in SQL Server is TRUE?

A UDFs can modify database state by performing INSERT, UPDATE, or DELETE operations.

B UDFs always execute faster than equivalent inline SQL queries.

C Scalar UDFs can call stored procedures that modify data.

D Table-valued UDFs can be used in JOINs like regular tables or views.

💡 Hint

Think about what UDFs are allowed to do and how they behave in queries.

Table-valued functions return tables and can be joined like tables or views. UDFs cannot modify data or call stored procedures that do.