Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 14 discussion

Actual exam question from Microsoft's 70-761

Question #: 14
Topic #: 1

You need to create a database object that meets the following requirements:
✑ accepts a product identified as input
✑ calculates the total quantity of a specific product, including quantity on hand and quantity on order
✑ caches and reuses execution plan
✑ returns a value
✑ can be called from within a SELECT statement
✑ can be used in a JOIN clause
What should you create?

A. an extended stored procedure
B. a user-defined scalar function
C. a user-defined stored procedure that has an OUTPUT parameter
D. a temporary table that has a columnstore index

Show Suggested Answer

Suggested Answer: B 🗳️
User-defined scalar functions are execution plans that accept parameters, perform an action such as a complex calculation, and returns the result of that action as a value. The return value can either be a single scalar value or a result set. Furthermore the execution plan is cached and reusable.
User-defined scalar functions can also be called from within a SELECT statement and can be used in a JOIN clause.
Incorrect Answers:
A: Using extended stored procedures is not recommended as they has been deprecated. CLR Integration should be used instead of extended stored procedures.
C: Stored procedures cannot be used in a SELECT statement or in a JOIN clause.
D: A temporary table is a result set and not a value.
References:
https://www.c-sharpcorner.com/UploadFile/996353/difference-between-stored-procedure-and-user-defined-functio/

by Oleksii_D at Jan. 21, 2020, 5:41 p.m.

Comments

Submit Cancel

Anirudh_net

3 years, 3 months ago

User-defined scalar functions are execution plans that accept parameters, perform an action such as a complex calculation, and returns the result of that action as a value. The return value can either be a single scalar value or a result set. Furthermore the execution plan is cached and reusable, B

upvoted 1 times

...

Vermonster

4 years, 5 months ago

UDF. You can use in JOIN: SELECT T1 INNER JOIN T2 ON T1.ID = T2.ID AND T1.Val = scalar UDF An extended case of JOIN but it fits everything listed

upvoted 1 times

...

Luzix

4 years, 6 months ago

Scalar valued function return single value (not table) so you cannot use joins. https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/create-user-defined-functions-database-engine?redirectedfrom=MSDN&view=sql-server-ver15

upvoted 1 times

...

HA2020

4 years, 7 months ago

it can be a user defined function..but not a user defined scalar function.

upvoted 1 times

...

Joeyboats

4 years, 11 months ago

This is correct a scaler function can be used in the join clause. They are not saying as a table join but as a clause.

upvoted 3 times

...

gtc108

5 years, 3 months ago

Scalar valued function return single value (not table) so you cannot use joins. If it is a table valued function you can use the following as another way then Using Apply.

upvoted 3 times

tcroots19

5 years, 3 months ago

You could use it in a join, as the value of something, couldn't you, very similar to FROM Y JOIN x ON x.key = y.key AND x.someFilterCol = dbo.functA(prodId) right?

upvoted 11 times

...

Barbedx

5 years, 4 months ago

But how user-defined SCALAR function can be used in JOIN clause? I think this imposible

upvoted 2 times

Hoglet

4 years, 6 months ago

I think the question is wrong. The question states that it can be called from the SELECT and used in the JOIN clause. Nothing here fits this, but the closest is a UDF. It can be used in a JOIN in the predicate, although not as the (virtual) table.

upvoted 1 times

...

AnsB

5 years, 4 months ago

I Think it is inlined.

upvoted 1 times

...

Oleksii_D

5 years, 4 months ago

Can a user-defined scalar function be inlined?

upvoted 1 times

...