4

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?

2 Answers 2

3

If this is just a performance question then you could consider writing your view like this:

CREATE VIEW Shipment_NEW AS

    SELECT  SHIPMENT_ID AS ShipmentId
    FROM    dbo.tblShipment_Old shipmentOld
    WHERE SHIPMENT_ID NOT IN (0, -1)

    UNION ALL

    SELECT  NULL AS ShipmentId
    FROM    dbo.tblShipment_Old shipmentOld
    WHERE SHIPMENT_ID IN (0, -1);

That allows index seeks when filtering on a value that isn't 0 or -1:

enter image description here

If what you actually wanted was to have a single column in a view that behaves differently when used in the WHERE clause and when used in the SELECT clause, then no, that isn't possible.

2
  • Yep, here is the link: brentozar.com/pastetheplan/?id=SJif9NOgm
    – Vaccano
    Jun 8, 2018 at 17:17
  • I played with a variation of what you had. Since I want 0 or -1 to show as null, I replaced the case statement with null as ShipmentId. When I did that, it worked!
    – Vaccano
    Jun 8, 2018 at 20:44
4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.