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
Explanation:
A table-valued parameter is scoped to the stored procedure, function, or dynamic TransactSQL 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 tablevalued 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.
Incorrect:
Not A: A scalar function would only be able to use other columns from the same table.
A
I just saw the “Not A” comment of the reply…
Actually, all we need is one column of the current table as argument of the scalar udf which will be the primary key (say OrderId) to calculate the totals from other tables which will obviously reference this table by its primary key.
Plus if that would be a problem, the same problem applies to the table-valued function, I don’t see the logic behind the answer
C is the correct ANS
agree with Pano.
within a function you could access any table you want.
see:
USE AdventureWorks2012;
GO
IF OBJECT_ID(N’dbo.fnGetTotalItems’, N’FN’) IS NOT NULL
DROP FUNCTION dbo.fnGetTotalItems;
GO
CREATE FUNCTION dbo.fnGetTotalItems (@OrderID INT)
RETURNS INT
WITH RETURNS NULL ON NULL INPUT,
SCHEMABINDING AS
BEGIN
DECLARE @TotalItems INT
SELECT @TotalItems = SUM(OrderQty)
FROM Sales.SalesOrderDetail
WHERE SalesOrderID = @OrderID
GROUP BY SalesOrderID
RETURN @TotalItems;
END;
GO