QuestionQ3
Secure, optimize, and deploy database solutionsYou have a SQL database in Microsoft Fabric that includes the following functions:
- A multi-statement table-valued function (TVF) named
Sales.mstvf_OrderStatus()that returns order status information. - A scalar user-defined function (UDF) named
dbo.ufn_GetTaxMultiplier (@TaxAmt money, @StateCode char(2))that returns a numeric multiplier used in tax calculations.
Reporting queries frequently join Sales.mstvf_OrderStatus() to Sales.SalesOrderHeader and return large result sets. A performance review shows that the queries generate inconsistent execution plans.
During a code review, a developer finds that the following Transact-SQL statement produced an error:
EXEC @ret = ufn_GetTaxMultiplier @TaxAmt = 100.00, @StateCode = ‘WA’;
For each of the following statements, select Yes if the statement is true. Otherwise, select No.
Yes or No
| Statements | Yes | No |
|---|---|---|
| You can use GETDATE() in dbo.ufn_GetTaxMultiplier to produce nondeterministic results. | ||
| Rewriting Sales.mstvf_OrderStatus() as an inline table TVF will reduce the number of inconsistent execution plans. | ||
| Replacing ufn_GetTaxMultiplier with dbo.ufn_GetTaxMultiplier in the EXEC function statement will resolve the error. |
Community Discussion