Exam 70-764 topic 1 question 46 discussion

Actual exam question from Microsoft's 70-764

Question #: 46
Topic #: 1

You have a database that stores information for a shipping company. You create a table named Customers by running the following Transact-SQL statement.
(Line numbers are included for reference only.)

You need to ensure that salespeople can view data only for the customers that are assigned to them.
Which Transact-SQL segment should you insert at line 07?

A. RETURNS varchar(20)WITH Schemabinding
B. RETURNS dbo.CustomersORDER BY @salesPerson
C. RETURNS tableORDER BY @salesPerson
D. RETURNS tableWITH Schemabinding

Show Suggested Answer

Suggested Answer: D 🗳️
The return value can either be a scalar (single) value or a table.
SELECT 1 just selects a 1 for every row, of course. What it's used for in this case is testing whether any rows exist that match the criteria: if a row exists that matches the WHERE clause, then it returns 1, otherwise it returns nothing.
Specify the WITH SCHEMABINDING clause when you are creating the function. This ensures that the objects referenced in the function definition cannot be modified unless the function is also modified.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql

by TheSwedishGuy at Jan. 24, 2020, 11:36 a.m.

Comments

Submit Cancel

Jkilleen

4 years, 9 months ago

Wont the function return all Customers or none at all (depending on whether the @salesperson parameter = the current user user_name() or not) - i.e. the WHERE statement is not referencing any column in the table. Maybe I am missing something?

upvoted 1 times

Hoglet

4 years, 5 months ago

No, you’re not mis-reading that. Could be a typo in the question though, but I doubt it.

upvoted 1 times

...

hiro4thap

4 years, 11 months ago

The answer seems nothing to do with the statement: 'view data only for the customers that are assigned to them.' which doesn't make a sense.

upvoted 1 times

...

TheSwedishGuy

5 years, 5 months ago

The line "RETURNS table WITH Schemabinding" locks the result set as is, and it cannot be modified directly, instead having to go through the function itself, if such functionality was implemented.

upvoted 2 times

...