Share via

Joins vs Functions and Performance

Don Thompson 0 Reputation points
2025-07-03T19:12:57.03+00:00

It is axiomatic that joining to another table from your primary table to retrieve data, using primary keys, provides better performance than using a function. At least that's what I know from the last time I cared about it years ago. It's time to care about it again.

Assume a primary table. In a query on this table, you want to include a tidbit of info from other table(s). I expect that if there is a primary key relationship between the two tables then a join is the way to go.

But if that tidbit is buried in many tables with complex joins, then I would think a function is a better way. But what does SQL Server think about it?

Is it better, performance wise, to throw all those other tables into the query with various complex joins or just create a function to "black box" that mess and simply provide an argument to retrieve the tidbit.

Of course there are many factors to consider that I am not including. Just looking for a rule of thumb.

SQL Server | SQL Server Transact-SQL
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 124.3K Reputation points MVP Volunteer Moderator
    2025-07-03T19:48:52.9233333+00:00

    From a performance point-of-view, writing the code directly against the tables is usually the better choice.

    As for involving function, it depends on what sort of function we are talking about. If it is an inline table-function, this is essentially a parameterised view, and as such it does cause any overhead, since a view/inline table function is just a macro that is pasted into the query, and the optimizer works with the expanded query text. There is a caveat though: It could be that the view involves more tables needed for the operation, or that you need a column from one of the tables that is not exposed in the view. This may lead to that you join in the table explicitly, and now there is overhead.

    For multi-statement table function and scalar functions, they typically always add overhead, since the function is an execution on its own, and a scalar function is typically executed row-by-row. True, since SQL 2019, scalar functions may be inlined, but there are many cases where this does not happen, so it is nothing you can assume.

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.