Luis Cazares - Monday, November 20, 2017 7:27 AM

I'm having an issue right now. We removed an identity column that is mostly irrelevant to a table. However, this identity is used by grids on the UI. To replicate it, I used a ROW_NUMBER to create an ID on the fly for the grid to use.
The view is something like this:

CREATE VIEW vwCustomData
AS
SELECT
  CustomData   = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
    ,PVID   = cd.PVID
    ,VarcharValue   = cd.VarcharValue
    ,NumericValue   = cd.NumericValue
    ,MoneyValue   = cd.MoneyValue
    ,DateValue    = ISNULL(cd.DateValue, CONVERT(DATE, '01-01-1900', 110))
    ,CustomDataLabel     = cdl.CustomDataLabel
    ,CustomDataDisplay = cdl.CustomDataDisplay
    ,DataClass    = cdc.DataClass
FROM dbo.tblCustomData AS cd(NOLOCK)
LEFT OUTER JOIN dbo.tblCustomDataLabels AS cdl(NOLOCK) ON cdl.CustomDataLabelID = cd.CustomDataLabelID
LEFT OUTER JOIN dbo.tblCustomDataClass AS cdc(NOLOCK) ON cdc.DataClassID = cdl.DataElementClassID

And it's usually called like this:

SELECT * FROM vwCustomData WHERE PVID = @ID;

The problem is that the ROW_NUMBER is being generated before filtering by PVID. Is there anyway to make it filter before generating the row numbers? I get that behavior if I use the query instead of the view.
The developer is not willing to change the usage of the view because it would might introduce problems if they miss one spot. They also don't like the degradation of performance (from >1 milliseconds to 2-3 seconds).
Any ideas?
The main table has several millions of rows.