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?