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

Question

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 indexes on Lot and TestGrade. However, it's my intuition that with the function approach where we insert into a variable and then feed it to a function, we lose these indexes and so with this join we're looking at O(n^2) performance. Is that a fair assessment? I'm concerned about performance as the the server is not robust hardware wise, and so anything to help with query execution is preferred.

Generally speaking, in this sort of scenario, is there a best practice method - is a view or a function the "obvious" or better choice?

Answer 1

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.

Answer 2

While I agree with the other answers that generally a View will have less potential to run into performance issues than a Function, it also depends specifically on the query one writes too. I mention this because option C is to use a View that leverages the ROW_NUMBER() window function to efficiently get the top record of each Lot like so:

WITH LotGradesSorted AS
(
    SELECT id, Lot, TestGrade, OtherColumns,
        ROW_NUMBER() OVER (PARTITION BY Lot ORDER BY TestGrade DESC) AS GroupSortId
    FROM dbo.Fills
)

SELECT id, Lot, TestGrade, OtherColumns
FROM LotGradesSorted
WHERE GroupSortId = 1

This generates a unique ID for each row within the grouping (PARTITION) on the Lot field ordered by the TestGrade field descending, such that the row with the largest TestGrade within each group of Lots is always the first row. The final SELECT query only returns those rows (with the WHERE GroupSortId = 1 filter).

Note that this only works when the field(s) in the ORDER BY clause of the window function are unique within the grouping on the field in the PARTITION clause (i.e. if you never have the same TestGrade duplicated within the same Lot). Otherwise, if the fields being ordered on are not unique, then the ordering is nondeterministic (because two rows will have the same exact values to order by so there's no consistent way to choose in a tie) which can result in semi-random results. But as long as your TestGrade field is unique within each grouping of Lots then you'll never have this issue.

The reason this query inside of your View can before more efficient than your proposed query is because it should result in less passes over your table, and Microsoft has designed window functions to be performant tools when used under the correct circumstances.

Answer 3

Your intuition is right. The view is the way to go. The function doesn't do anything else than a simple SELECT statement, no procedural logic involved at all.

A function can be used too, but it would be much better to define it as an inline table-valued function:

CREATE FUNCTION dbo.fnFills()
RETURNS TABLE
RETURN(
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]
);

Here is a good explanation of difference between table value function and inline table value functions.

But the function doesn't give any advantage over the view in this situation, so I would just stick with a good old view.

Note: I took your query as is, but it's considered best practice to avoid "SELECT *" statements.