You need to create an indexed view that requires logic statements to manipulate the data that the view displays.
Which two database objects should you use? Each correct answer presents a complete solution.
A.
a user-defined table-valued function
B.
a CRL function
C.
a stored procedure
D.
a user-defined scalar function
Explanation:
You can create a database object inside an instance of SQL Server that is programmed in an assembly created
in the Microsoft .NET Framework common language runtime (CLR). Database objects that can leverage the
rich programming model provided by the common language runtime include aggregate functions, functions,
stored procedures, triggers, and types.
i would say answer A and D , You cannot call a stored proc from inside a view
13
0
Agree. An inline table-valued function can perform as a parameterized view, but is still not allowed to call an SP like this.
4
1
AD
1
0
scalar function and table value function
Use this statement to create a reusable routine that can be used in these ways:
To parameterize a view or improve the functionality of an indexed view
fuente:Microsoft
2
0
I’m Afraid the answer is wrong.
and so are the replies.
WHY?
Indexed View cannot reference inline or multistatement user defined functions.
Cannot create index on view “VIEW NAME” because it references the inline or multistatement table-valued function “FUNCTION NAME”. Consider expanding the function definition by hand in the view definition, or not indexing the view.
as we cannot use the stored procedure in the View only two options left
B) and D)
[TEST BY YOURSELF (TSQLV4 Database)]
DROP FUNCTION IF EXISTS HR.GetManagers;
GO
–Creating the function (in this case it is a multistatment tvf)
CREATE OR ALTER FUNCTION HR.GetManagers(@empid AS INT) RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
WITH EmpsCTE AS
(
SELECT empid, mgrid, firstname, lastname, 0 AS distance
FROM HR.Employees
WHERE empid = @empid
UNION ALL
SELECT M.empid, M.mgrid, M.firstname, M.lastname, S.distance + 1 AS distance
FROM EmpsCTE AS S
JOIN HR.Employees AS M ON S.mgrid = M.empid
)
SELECT empid, mgrid, firstname, lastname, distance
FROM EmpsCTE;
GO
–Creating the view
CREATE OR ALTER VIEW NAC_VW
WITH SCHEMABINDING
AS
SELECT empid FROM HR.GetManagers(9) AS M;
GO
–will get error
CREATE UNIQUE CLUSTERED INDEX CL ON NAC_VW(empid)
9
0
A user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. The return value can either be a scalar (single) value or a table. Use this statement to create a reusable routine that can be used in these ways:
To parameterize a view or improve the functionality of an indexed view
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-function-transact-sql?view=sql-server-2017
1
0
i confirm B and D.
5
3
CRL function no existe. Es CLR FUNCTION
https://docs.microsoft.com/es-es/sql/relational-databases/user-defined-functions/create-clr-functions?view=sql-server-ver15
2
0