Views versus a function for getting the top records of a table?

I'm talking with a coworker about a way to approach a problem.

We have a table that keeps track of a filling process, that has unique index columns (or should at least) on columns Lot and TestGrade.

Recently, we added logic so that when a TestGrade of anything higher than 1 is created, all the lower levels. Some logic is done so the lower level rows are not necessarily identical to the top level.

For example, I INSERT something with Lot='ABCD' and TestGrade=5 then in my Fills table I see

id    Lot    TestGrade   OtherColumns
======================================
10    'ABCD'     1          blah3
9     'ABCD'     2          bar
8     'ABCD'     3          foo
7     'ABCD'     4          blah1
6     'ABCD'     5          blah

Now the issue is in many instances, I only want the top level records of of the Fills table, so in the example, I would only want the record with id=6.

We have two ideas on how to do this. I want to make a view that looks like

CREATE VIEW [dbo].[vwFills]
AS
SELECT t.* FROM [dbo].[Fills] t
JOIN (SELECT [Lot], MAX(TestGrade) as TestGrade FROM dbo.Fills GROUP BY [Lot]) t2 ON t.[Lot] = t2.[Lot] AND t.[TestGrade] = t2.[TestGrade]

My coworker wants to do something similar but inside a function that will look like this

ALTER FUNCTION [dbo].[fnFills] (
 @Fills tyFills READONLY
 )
RETURNS @returnTable TABLE(
//Copy of the table definition of Fills, without
    id INT NOT NULL,
    Lot VARCHAR(10) NOT NULL,
    TestGrade INT NOT NULL,
    //rest of the columns..
    )
AS

Begin

insert into @returnTable
    select t2.* from (
        select [Lot], max([TestGrade]) as TestGrade from @Fills) t1
            left join Fills t2
            on t1.[Lot] = t2.[Lot] and t1.[TestGrade] = t2.[TestGrade]

Return

End

Then to call this function, it would look like

DECALARE @FillRecords tyFills;
INSERT INTO @FillRecords SELECT * FROM db.Fills;
SELECT * FROM dbo.fnFills(@FillRecords);

Our dbo.Fills does have

functions in sql server are usually bad ideas

Functions in SQL Server, specifically scalar, and multi-statement table valued functions, have a lot of issues. I'm going to skip over scalar UDFs, since that's not what your question is about.

For the type of function your co-worker suggested, a multi-statement table valued function, the issue is mostly around the @table variable that backs it up. The problems you'll have here are:

• Insert query will be forced to run single-threaded

• Lousy row estimates when @table variable is queried

• No column-level statistics about the @table variable contents

Even indexing the @table variable wouldn't be terribly useful for you here.

You could avoid most of the issues by using an inline table-valued function. Their performance is only as bad as the query you put in them, and the supporting indexes available.

CREATE OR ALTER FUNCTION 
    dbo.fnFills_Inline 
(
    @Fills tyFills READONLY
)
RETURNS table
AS
RETURN

    SELECT 
        t2.* 
    FROM 
    (
        SELECT 
            [Lot], 
            MAX([TestGrade]) AS TestGrade 
        FROM @Fills
     ) t1
     LEFT JOIN Fills t2
         ON  t1.[Lot] = t2.[Lot] 
         AND t1.TestGrade = t2.[TestGrade];

There should be no material difference between the view and function in this case, but in views where window functions are involved, an inline table-valued function may be necessary to push a predicate past the sequence project operator.

StackExchange Database Administrators Q#303022, answer score: 3