You are a database developer on an instance of SQL Server 2008. Your Prod database contains an InventoryHistory table that contains a historical record of all inventory transactions that have occurred in your warehouse.
You need to allow users to access specific data from the InventoryHistory table, but not to make updates. You want to allow users to provide three different search criteria values and retrieve historical inventory information based on the values provided. Data will be retrieved from the InventoryHistory table and other lookup tables based on the user’s specified criteria. Users must then be able to use this data to perform additional analysis.
You want to accomplish this with minimal development effort. Which action should you take?
A. Create a recursive scalar user-defined function.
B. Create an inline table-valued function.
C. Create a partitioned view on the InventoryHistory table.
D. Create a multi-statement table-valued function.
Correct Answer: B
Explanation/Reference:
In SQL Server 2008, user-defined functions may be one of three types: scalar-valued, inline table-valued, or multi-statement table-valued. Both inline tablevalued and multi-statement table-valued functions can be used to return a result set as a table data type. Inline table-valued functions can execute only one SQL statement. Multi-statement table-valued functions can execute multiple SQL statements as well as include other procedural code. An inline table-valued function can be an alternative to creating a view, and provides the benefit of being able to specify parameters within the SELECT statement. In this scenario, because the user-defined function will not include multiple SQL statements and only needs to return rows based on the parameters it was passed, you should use an inline tablevalued function.
To create an inline table-valued function, use the CREATE FUNCTION statement and specify a table data type as the return value. Unlike a multi-statement tablevalued function, an inline table-valued function does not have to define the structure of the table being returned. The structure of the returned table is derived by the SELECT statement included within the function. For example, the following statement would create an inline table-valued function that accepts two parameters and returns a table of data based on a query of two tables:
CREATE FUNCTION dbo.GetData
(
@param1 varchar(100), @param2 varchar(100),
@param2 varchar(100), @param3 varchar(100)
)
RETURNS table
AS
RETURN (
SELECT *
FROM InventoryHistory
WHERE Field1 = @param1 AND Field2 = @param2 AND Field3 = @param3
);
This would create an inline table-valued function named dbo.GetData that users could use to query the InventoryHistory table. Users would be able to call the function from within a FROM clause, passing it the search parameters to return the data and perform additional analysis.
SELECT * FROM dbo.GetData(‘A’, ‘B’, ‘C’);
You should not create a recursive scalar user-defined function. A scalar user-defined function returns a single scalar value, and in this scenario, you want users to access a result set.
You should not create a multi-statement table-valued function because this would require more development effort. With a multi-statement table-valued function, you must explicitly define the table being returned, but with an inline table-valued function, you do not.
You should not create a partitioned view on the InventoryHistory table. A partitioned view is used when you have similar data stored in multiple tables and want to create a view to allow access to all of the data as if it were stored in a single table. Partitioned views are implemented using the UNION ALL operator. For example, if you had three separate tables with an identical structure, you might use the following statement to create a partitioned view that allows users to query data from all three tables:
CREATE VIEW PartView
AS
SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2
UNION ALL
SELECT * FROM Table3;
