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?