SQL Server 2019 Query Issue - Scalar Procedure

Virtual-Dude 1 Reputation point
2022-03-10T19:46:01.35+00:00

Hello community.

I have a weird issue that I am not SQL guru enough to figure out.

I have a stored scalar procedure for a database that does a TOP 95 PERCENT function of bandwidth on an interface. with 4 variables of @InterfaceID, @StartDate, @EndDate, @Percentile


BEGIN
DECLARE @ResultVar real

SELECT @ResultVar = MAX(In_Averagebps)
FROM (
SELECT TOP(@Percentile) PERCENT In_Averagebps
FROM dbo.InterfaceTraffic WITH (NOLOCK)
WHERE InterfaceID = @InterfaceId AND DateTime >= @StartDate AND DateTime <= @EndDate
ORDER BY In_Averagebps ASC
) AS AA

RETURN @ResultVar

END


This works just fine if I run this selection in a query and PRINT out the result.

If I run a normal query against the database and reference the scalar procedure such as:


SELECT
dbo.MyScalarProcedure(@InterfaceID, @StartDate, @EndDate, @Percentile) AS Maxbps_In95 (and actually fill in the variables correctly). The query just selects the TOP 95 records instead of actually doing the 95th percent of all records.

I can see using the Query Execution Plan details that when I call upon the procedure it behaves this way. If I right click and select properties on the "Top" function step of the query in the execution plan view, it says "Is Percent" false and is obvisouly selecting 95 of 95 rows.....

I dont know why it works when I run the procedure itself selectively, but when referencing the procedure from a query....it just totally forgets about the whole 95 PERCENT command in the SELECT statement and just grabs the TOP 95 rows.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,924 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,565 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Tom Cooper 8,466 Reputation points
    2022-03-11T05:11:12.077+00:00

    I agree it's a bug. Here is a reproduction (I simplified the code somewhat). I'm running SQL 2019 CU15.

    Use tempdb
    go
    -- Create table with 200 rows vith values from 1-200
    Create Table dbo.InterfaceTraffic(In_Averagebps real);
    go
    ;With cte As
    (Select Cast(1 As real) As r
    Union All 
    Select r+1. As r
    From cte
    Where r<200)
    Insert dbo.InterfaceTraffic(In_Averagebps)
    Select r
    From cte
    Option (Maxrecursion 300);
    go
    
    -- Create Function
    Create Function dbo.SampleProc() 
    Returns real
    As
    BEGIN
    DECLARE @ResultVar real
    
    SELECT @ResultVar = MAX(In_Averagebps)
    FROM (
    SELECT TOP 95 PERCENT In_Averagebps
    FROM dbo.InterfaceTraffic
    ORDER BY In_Averagebps ASC
    ) AS AA
     RETURN @ResultVar
    END
    go
    
    -- Call to function returns incorrect result 95, should be 190
    Declare @ReturnedResult real;
    Select dbo.SampleProc() As BadResult
    go
    
    -- Executing same code outside of functions returns correct result 190
    DECLARE @ResultVar real
    SELECT @ResultVar = MAX(In_Averagebps) 
    FROM (
    SELECT TOP 95 PERCENT In_Averagebps
    FROM dbo.InterfaceTraffic
    ORDER BY In_Averagebps ASC
    ) AS AA
    Select @ResultVar As GoodResult
    go
    
    --Cleanup
    Drop Function dbo.SampleProc;
    go
    Drop Table dbo.InterfaceTraffic;
    

    Tom

    1 person found this answer helpful.

  2. Erland Sommarskog 102.4K Reputation points
    2022-03-10T22:25:17.073+00:00

    Sounds like a bug to me. What is the output of "SELECT @@version"?

    I see that you have NOLOCK in that function. Please beware of that with NOLOCK there are many things that can go wrong. Not only can you read uncommitted dirty data, but you can also fail to read data that has been committed, or you may read the same data twice. This can lead to transient error that are difficult to reproduce.

    0 comments No comments

  3. LiHong-MSFT 10,046 Reputation points
    2022-03-11T05:52:25.713+00:00

    Hi @Virtual-Dude
    What I'm confused about this issue is how you referencing the stored procedure in the select statement.Or what you mean is UDF?
    Then I tested on my own sqlsever, and the issue you describe does not appear.
    So as the experts answered above, I guess it's a bug.

    Best regards,
    LiHong

    0 comments No comments