Jul 13  

Sometimes this is me

     Jul 13  

Sometimes this is me

     Jul 13  

Sometimes this is me

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;

     Jul 13  

Sometimes this is me