Exam 70-464 topic 1 question 93 discussion

Actual exam question from Microsoft's 70-464

Question #: 93
Topic #: 1

You have a Microsoft SQL Azure database named DBAzurel. DBAzurel contains a table named Orders that stores sales data.
Each order has a sales total that can only be discovered by querying multiple tables.
You need to ensure that the value of the sales total is returned by executing a query on Orders.
What should you create?

A. A calculated column that uses a scalar function
B. A trigger that uses a table-valued function
C. A calculated column that uses a table-valued function
D. A trigger that uses a ranking function

Show Suggested Answer

Suggested Answer: C 🗳️
A table-valued parameter is scoped to the stored procedure, function, or dynamic Transact-SQL text, exactly like other parameters. Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement. You can declare table-valued variables within dynamic Transact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions.
Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters.

by Luzix at Nov. 4, 2020, 3:18 p.m.

Comments

Submit Cancel

Luzix

Highly Voted 4 years, 7 months ago

Use table function in computed column? No, you can't do that. The reason is that a column (whether computed or not) can only contain one value. And any table function can potentially return multiple values. Even if it only returns one column. it might return multiple rows. Maybe you know it only returns one row, but SQL doesn't know that. And if you know it returns only one row and only one column, why not make it a scalar function?

upvoted 5 times

...