Sargability of queries against a view with a case expression

I have a view that looks similar to this:

CREATE VIEW Shipment.Shipment AS

    SELECT  CASE 
                WHEN shipmentOld.SHIPMENT_ID = 0 OR shipmentOld.SHIPMENT_ID = -1 THEN NULL
                ELSE shipmentOld.SHIPMENT_ID
            END AS ShipmentId, 

            OtherValue, SomeOtherValue, OtherStuff
    FROM    dbo.tblShipment_Old shipmentOld
GO

Then if I do a query like this:

SELECT * FROM Shipment.Shipment WHERE ShipmentId = 18140

I get a full index scan. The reason why is that it is running the case expression for ShipmentId on all the rows in the table and comparing the result to my ShipmentId (18140). The very definition of non-sargable.

I am using this view to normalize legacy data so I can write a new application on top of it. I just want the case expression to run on output.

I am guessing that is not possible, but I thought I would ask before I pursue more extreme options. So here is my question:

Is it possible to still have my ShipmentId output go through the case expression, but also have a sargable query when ShipmentId is used in the where clause?

Is it possible to still have my ShipmentId output go through the case
expression, but also have a sargable query when ShipmentId is used in
the where clause?

No. SQL Server doesn't do that for you.

This is where you might use an inline Table-Valued Function, sometimes called a "parameterized view". EG

CREATE function GetShipments ( @ShipmentId int )
returns table
AS return

    SELECT  CASE 
                WHEN shipmentOld.SHIPMENT_ID = 0 OR shipmentOld.SHIPMENT_ID = -1 THEN NULL
                ELSE shipmentOld.SHIPMENT_ID
            END AS ShipmentId, 

            OtherValue, SomeOtherValue, OtherStuff
    FROM    dbo.tblShipment_Old shipmentOld
    where  SHIPMENT_ID = @ShipmentID

Or add an extra column to your view.

StackExchange Database Administrators Q#209025, answer score: 4