DRAG DROP
You use Microsoft SQL Server 2012 to develop a database application.
You create a table by using the following definition:
CREATE TABLE Prices (
PriceId int IDENTITY(1,1) PRIMARY KEY,
ActualPrice NUMERIC(16,9),
PredictedPrice NUMERIC(16,9)
)
You need to create a computed column based on a user-defined function named
udf_price_index. You also need to ensure that the column supports an index.
Which three Transact-SQL statements should you use? (To answer, move the appropriate
SQL statements from the list of statements to the answer area and arrange them in the
correct order.)
Answer: 
Explanation:
The proposed solution does’n works: it isn’t possible to create an index on non-precise computed column not persisted. The computed column must be precise or must be presisted.
0
0
wops sorry: ignore previous post please. 🙂
0
0
CREATE FUNCTION udf_price_index
(
@actualprice NUMERIC(16, 9),
@predictedprice NUMERIC(16, 9)
)
RETURNS NUMERIC(16, 9)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @priceindex NUMERIC(16, 9);
SELECT @priceindex = CASE
WHEN @predictedprice = 0 THEN
0
ELSE
@actualprice / @predictedprice
END;
RETURN @priceindex;
END;
GO
ALTER TABLE Prices ADD [Priceindex] AS dbo.udf_price_index([ActualPrice], [Predictedprice]) PERSISTED;
0
0