Sql-server – Creating a Persisted Computed Column with a function

computed-columnsql serversql-server-2008

I am working with the programmers on a database solution. They want to add a computed column to mimic the old keys for the older queries, procedures, and systems and index it. The new keys will be GUIDS.

To do this, they want to create a function for the computed column that creates a value and persist it. It will not let them persist the column. I don't have any warm fuzzies about the idea and I also can not find any info on the web about the technique (is it a technique?).

I am thinking they need to add a trigger instead. Does anyone have any ideas?

The function will be run as this:

(SELECT [INT Identity field] FROM TABLE WHERE [GUID COLUMN] = @GUIDKEY

It returns an INT Identity field based on the GUID.

This will be run on ever insert into a related table. SO if Table One holds the primary key, the related table Two will update (using the GUID passed in) to get the key from the Table one and insert it into table two.

Best Answer

Still don't understand why this needs to be a column in a table, never mind a persisted one.

Why not just create a table-valued function that you cross apply when (and only when) the query actually needs it? Since the old key will never change it doesn't need to be computed or persisted anyway.

If you really want the old key to live in multiple places (and it sounds like people who shouldn't be making this kind of decision have already made this kind of decision), just do the lookup in a trigger and populate it at write time. Then it's just a static column in a table.

I still highly recommend a table-valued function to facilitate this, so that you can write the trigger in such a way that it handles multi-row operations... without having to write a loop, or call a scalar-valued function over and over again for every row.

Just to show how similar these things really are (and question your lead developer who "doesn't like it"):

-- bad, slow, painful row-by-row
CREATE FUNCTION dbo.GetIDByGUID
(
  @GuidKey uniqueidentifier
)
RETURNS int
AS
BEGIN
  RETURN (SELECT $IDENTITY FROM dbo.tablename WHERE guid_column = @GuidKey);
END
GO

-- in the trigger:
UPDATE r SET oldkey = dbo.GetIDByGUID(i.guid_column)
  FROM dbo.related AS r
  INNER JOIN inserted AS i
  ON r.guid_column = i.guid_column;

Now, if you have a table-valued function, the code is quite similar, but you'll find the performance is much better in multi-row operations, and close to identical for single-row operations.

-- ah, much better
ALTER FUNCTION dbo.GetIDByGUID_TVF
(
  @GuidKey uniqueidentifier
)
RETURNS TABLE
AS
  RETURN (SELECT id = $IDENTITY FROM dbo.tablename WHERE guid_column = @GuidKey);
GO

-- in the trigger:
UPDATE r SET oldkey = f.id
  FROM dbo.related AS r
  INNER JOIN inserted AS i
  ON r.guid_column = i.guid_column
  CROSS APPLY dbo.GetIDByGUID_TVF(i.guid_column) AS f;

Explanation

The real question is why the optimizer felt the need to retrieve A, B, and C for the index seek at all. We would expect it to read the Comp column using a nonclustered index scan, and then perform a seek on the same index (alias T2) to locate the Top 1 record.

The query optimizer expands computed column references before optimization begins, to give it a chance to assess the costs of various query plans. For some queries, expanding the definition of a computed column allows the optimizer to find more efficient plans.

When the optimizer encounters a correlated subquery, it attempts to 'unroll it' to a form it finds easier to reason about. If it cannot find a more effective simplification, it resorts to rewriting the correlated subquery as an apply (a correlated join):

Apply rewrite

It just so happens that this apply unrolling puts the logical query tree into a form that does not work well with project normalization (a later stage that looks to match general expressions to computed columns, among other things).

In your case, the way the query is written interacts with internal details of the optimizer such that the expanded expression definition is not matched back to the computed column, and you end up with a seek that references columns A, B, and C instead of the computed column, Comp. This is the root cause.

Workaround

One idea to workaround this side-effect is to write the query as an apply manually:

SELECT
    T1.ID,
    T1.Comp,
    T1.D,
    CA.D2
FROM dbo.T AS T1
CROSS APPLY
(  
    SELECT TOP (1)
        D2 = T2.D
    FROM dbo.T AS T2
    WHERE
        T2.Comp = T1.Comp
        AND T2.D > T1.D
    ORDER BY
        T2.D ASC
) AS CA
WHERE
    T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY
    T1.Comp;

Unfortunately, this query will not use the filtered index as we would hope either. The inequality test on column D inside the apply rejects NULLs, so the apparently redundant predicate WHERE T1.D IS NOT NULL is optimized away.

Without that explicit predicate, the filtered index matching logic decides it cannot use the filtered index. There are a number of ways to work around this second side-effect, but the easiest is probably to change the cross apply to an outer apply (mirroring the logic of the rewrite the optimizer performed earlier on the correlated subquery):

SELECT
    T1.ID,
    T1.Comp,
    T1.D,
    CA.D2
FROM dbo.T AS T1
OUTER APPLY
(  
    SELECT TOP (1)
        D2 = T2.D
    FROM dbo.T AS T2
    WHERE
        T2.Comp = T1.Comp
        AND T2.D > T1.D
    ORDER BY
        T2.D ASC
) AS CA
WHERE
    T1.D IS NOT NULL -- DON'T CARE ABOUT INACTIVE RECORDS
ORDER BY
    T1.Comp;

Now the optimizer does not need to use the apply rewrite itself (so the computed column matching works as expected) and the predicate is not optimized away either, so the filtered index can be used for both data access operations, and the seek uses the Comp column on both sides:

Outer Apply Plan

This would generally be preferred over adding A, B, and C as INCLUDEd columns in the filtered index, because it addresses the root cause of the problem, and does not require widening the index unnecessarily.

Persisted computed columns

As a side note, it is not necessary to mark the computed column as PERSISTED, if you don't mind repeating its definition in a CHECK constraint:

CREATE TABLE dbo.T 
(   
    ID integer IDENTITY(1, 1) NOT NULL,
    A varchar(20) NOT NULL,
    B varchar(20) NOT NULL,
    C varchar(20) NOT NULL,
    D date NULL,
    E varchar(20) NULL,
    Comp AS A + '-' + B + '-' + C,

    CONSTRAINT CK_T_Comp_NotNull
        CHECK (A + '-' + B + '-' + C IS NOT NULL),

    CONSTRAINT PK_T_ID 
        PRIMARY KEY (ID)
);

CREATE NONCLUSTERED INDEX IX_T_Comp_D
ON dbo.T (Comp, D) 
WHERE D IS NOT NULL;

The computed column is only required to be PERSISTED in this case if you want to use a NOT NULL constraint or to reference the Comp column directly (instead of repeating its definition) in a CHECK constraint.

Best Answer

Related Solutions

Sql-server – Converting Database from GUID to Integer Keys

Sql-server – Index on Persisted Computed column needs key lookup to get columns in the computed expression

Explanation

Workaround

Persisted computed columns

Related Question