Code to demonstrate the issue, and show that UDFs that access data cannot be persisted:

CREATE TABLE dbo.Data

(

item INTEGER NOT NULL PRIMARY KEY,

value INTEGER NOT NULL,

);

INSERT dbo.Data (item, value)

SELECT V.number,

RAND(CHECKSUM(NEWID())) * 1000000

FROM master.dbo.spt_values V

WHERE V.type = N'P ';

GO

CREATE FUNCTION dbo.BadFunction(@item INTEGER)

RETURNS INTEGER

WITH SCHEMABINDING

AS

BEGIN

RETURN

(

SELECT D.value

FROM dbo.Data D

WHERE D.item = @item

)

END;

GO

-- Error if PERSITED keyword is uncommented:

-- Msg 4934, Level 16, State 3, Line 1

-- Computed column 'value' in table 'BadIdea' cannot be persisted

-- because the column does user or system data access.

CREATE TABLE dbo.BadIdea

(

row_id INTEGER PRIMARY KEY,

value AS

dbo.BadFunction (row_id)

--PERSISTED

);

GO

INSERT dbo.BadIdea(row_id)

SELECT V.number

FROM master.dbo.spt_values V

WHERE V.type = N'P ';

GO

-- Trace in Profiler to see separate function call

-- and query execution per row. (Does not show in

-- SSMS actual query plan).

-- Trace SQL:Batch Starting and SP:Starting

-- Shows 2048 separate calls to the UDF BadFunction.

-- Notice how long this simple query takes to run

-- on only 2048 rows.

SELECT MIN(value)

FROM dbo.BadIdea;

GO

DROP TABLE dbo.BadIdea;

DROP FUNCTION dbo.BadFunction;

DROP TABLE dbo.Data;