Functions

Question 1

What are the 2 types of user-defined functions (UDFs) that SQL Server supports?

Answer 1

Scalar and Table-valued

Question 2

Can functions update or change a database?

Answer 2

No

Question 3

What is a scalar function?

Answer 3

Returns a single value

Question 4

What does inlined mean?

Answer 4

The compiler will automatically incorporate the whole body of the function into the surrounding code

Question 5

What is interpreted code?

Answer 5

compiler invoking it from the main body of code requires a jump to a different code block to execute the function.

Question 6

What is the biggest potential drawback of SQL Server Scalar functions?

Answer 6

SQL Server will execute the function once for every row in the result set

Question 7

How many times are Table Valued Functions called?

Answer 7

SQL Server will call them only once, regardless of the number of rows in the result set

Question 8

What is deterministic?

Answer 8

Given the same inputs will return the same result each time.

Question 9

Give an example of a non-deterministic function?

Answer 9

GetDate()

Question 10

How many times are non-deterministic functions called?

Answer 10

Once per statement

Question 11

How many times are deterministic functions called

Answer 11

Once per row

Question 12

Why use schema binding?

Answer 12

Makes it more difficult to make changes to the underlying data structures that would break our functions

Question 13

How can you prevent function processing data when a null is provided for an input

Answer 13

Use the RETURNS NULL ON NULL INPUT option, which will cause SQL Server to immediately return NULL

Question 14

How can you tell if a scalar function is being called once for every row in dataset

Answer 14

Look at the query execution plan. if in the properties it references the function name (rather than the column name), then the function is being called once per row.

Question 15

Are there performance implication to using a scalar function in the WHERE clause?

Answer 15

Yes, If you are using an indexed column within the scalar function then this can lead to a full table scan rather than use of the index

Question 16

Where can a function be used?

Answer 16

Almost anywhere that we would use a table or column

Question 17

How does using multiple statements in a table valued functions affect performance

Answer 17

It treats them rather like a table for which it has no available statistics. As a result, even a very simple multi-statement TVF can cause severe performance problems.

Question 18

How can you avoid the performance cost of row by row execution of a scalar user defined function

Answer 18

Convert it into a single statement table valued function

Question 19

How can you improve the performance of a multi statement table valued function

Answer 19

Use a CTE (Common table expression)