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
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi