• Yet other options include:

    1. Creating a view (possibly indexed) over PSPerson and adding a column which contains the CASE.

    2. Adding a computed column for the CASE to the PSPerson table, and preferably persisting it.

    If you wish, you can encapsulate the CASE (without the data access) in a function and use that in the persisted computed column definition. An example of this idea follows:

    USE tempdb

    GO

    CREATE FUNCTION dbo.f (@p1 INT, @p2 INT)

    RETURNS INT

    WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT

    AS BEGIN RETURN CASE WHEN @p1 = 1 AND @p2 = 1 THEN 0 WHEN @p1 = 1 AND @p2 = 2 THEN 1 WHEN @p1 = 2 AND @p2 = 1 THEN 2 ELSE 3 END END

    GO

    CREATE TABLE dbo.Demo (A INT NOT NULL, B INT NOT NULL, C AS dbo.f (A, B) PERSISTED);

    GO

    INSERTdbo.Demo (A, B)

    SELECTTOP (25000)

    CONVERT(INT,RAND(CHECKSUM(NEWID())) * 2 + 1),

    CONVERT(INT,RAND(CHECKSUM(NEWID())) * 2 + 1)

    FROMmaster.sys.columns c1

    CROSS

    JOINmaster.sys.columns c2

    GO

    SELECT*

    FROMdbo.Demo;

    GO

    DROP TABLE dbo.Demo;

    DROP FUNCTION dbo.f;

    Paul