Why is my internal UDF table so slow when I use variable parameters rather than constant parameters?

Question

I have a tabular, embedded UDF. I want to filter the results of this UDF to get one specific value. When I specify a filter using a constant parameter, everything is fine, and the performance is almost instantaneous. When I specify a filter using a variable parameter, it takes significantly longer, about 500 times more logical readings and 20 times longer.

The execution plan shows that in the case of a variable parameter, the filter is not applied until the very end of the process, causing several index scans, not those that are performed in the constant case.

I think my questions are: why, since I specify one filter parameter that will be very selective with respect to the indexed field, does my performance weed when this parameter is in a variable? Can I do anything about this?

Does it have anything to do with the analytic function in the query?

Here are my queries:

CREATE FUNCTION fn_test()
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN
    SELECT DISTINCT GCN_SEQNO, Drug_package_version_ID
    FROM
    (
        SELECT COALESCE(ndctbla.GCN_SEQNO, ndctblb.GCN_SEQNO) AS GCN_SEQNO,
            dpv.Drug_package_version_ID, ROW_NUMBER() OVER (PARTITION BY dpv.Drug_package_version_id ORDER BY 
                ndctbla.GCN_SEQNO DESC) AS Predicate
        FROM dbo.Drug_Package_Version dpv
            LEFT JOIN dbo.NDC ndctbla ON ndctbla.NDC = dpv.Sp_package_code
            LEFT JOIN dbo.NDC ndctblb ON ndctblb.SPC_NDC = dpv.Sp_package_code
    ) iq
    WHERE Predicate = 1
GO

GRANT SELECT ON fn_test TO public
GO

-- very fast
SELECT GCN_SEQNO
FROM dbo.fn_test()
WHERE Drug_package_version_id = 10000

GO

-- comparatively slow
DECLARE @dpvid int
SET @dpvid = 10000
SELECT GCN_SEQNO
FROM dbo.fn_test()
WHERE Drug_package_version_id = @dpvid

Answer 1

The answers I received were good, and I learned from them, but I think I found an answer that satisfies me.

I really think that using the PARTITION BY clause is causing the problem here. I reformulated UDF using a variant of the self-connecting idiom:

SELECT t1.A, t1.B, t1.C
FROM T t1
    INNER JOIN
    (
        SELECT A, MAX(C) AS C
        FROM T
        GROUP BY A
    ) t2 ON t1.A = t2.A AND t1.C = t2.C

, , SQL- , , . , SQL, . UDF , SQL 2000.

, !

Answer 2

UDF, , - , . ( UDF ), .

, , .

, , , , , .

Answer 3

. - . Predicate = 1

casparOne, , dpv.Drug_package_version_id iq.

, PARTITION BY, dpv.Drug_package_version_id. ...TOP 1 ... ORDER BY ndctbla.GCN_SEQNO DESC.