Using table valued function as a table in sql server join

Question

I have a scenario where SQL server using table valued function with joins to many other tables. The function returns many columns, which are not always desirable output for every report, causing lots of IO.

Is it better to copy the query inside the function and output the result to temp table (with required columns and filter conditions only) and Join to other tables as required or is it OK to use the table valued function?

I understand generalizing code inside Table valued function is good attempt to control making code changes every where if logic changes.

Generalizing code and performance are both important to me, should I choose between them or is there other ways to improve performance in SQL server in such cases ?

Below is the sample query I often use with sample table valued function

--table valued function(used by many other sps as general procedure)
CREATE FUNCTION [dbo].[TfPersonDataSel]
( @UserPersonId INT )
RETURNS TABLE
AS
    RETURN ( SELECT p.PersonId ,
                    p.FirstName ,
                    p.MiddleName ,
                    p.LastName ,
                    p.[Name] ,
                    p.Name2 ,
                    p.SSN ,
                    p.TIN ,
                    p.OrganizationId ,
                    p.Title ,
                    p.UserPersonId ,
                    p.InsertDate ,
                    p.ReferenceId ,
                    p.ReferenceNote ,
                    o.OfficeId ,
                    o.Office ,
                    c.Alias ,
                    c.OrganizationId AS TenantOrganizationId ,
                    z.Organization ,
                    li.ListItem AS Entity ,
                    u.UserName ,
                    u.[Password] ,
                    li2.ListItem AS UserStatus ,
                    mp.[Value] Phone ,
                    em.[Value] Email ,
                    a.AddressId ,
                    a.Address1 ,
                    a.Address2 ,
                    a.City ,
                    a.ZipCode ,
                    a.StateId ,
                    s.StateCode ,
                    pdt.[Date] AS HiredDate ,
                    pe.DateOfBirth
             FROM   dbo.Person AS p
                    INNER JOIN dbo.PersonCurrent AS pc ON p.PersonId = pc.PersonId
                    INNER JOIN dbo.[User] AS u ON p.PersonId = u.PersonId
                    INNER JOIN dbo.Office AS o ON pc.OfficeId = o.OfficeId
                    INNER JOIN dbo.TenantOrganization AS c ON o.OrganizationId = c.OrganizationId
                    INNER JOIN dbo.Organization AS z ON c.OrganizationId = z.OrganizationId
                    INNER JOIN dbo.ListItem AS li ON pc.EntityListItemId = li.ListItemId
                    INNER JOIN dbo.ListItem AS li2 ON u.StatusListItemId = li2.ListItemId
                    LEFT JOIN dbo.PersonEEO AS pe ON pe.PersonId = p.PersonId
                    LEFT JOIN dbo.Address AS a ON pc.AddressId = a.AddressId
                    LEFT JOIN dbo.State AS s ON s.StateId = a.StateId
                    LEFT JOIN dbo.ContactInformation AS mp ON mp.ContactInformationId = pc.PhoneContactInformationId
                    LEFT JOIN dbo.ContactInformation AS em ON em.ContactInformationId = pc.EmailContactInformationId
                    LEFT JOIN dbo.PersonDateType AS pdt
                              INNER JOIN dbo.ListItem AS li3 ON  li3.ListItemId = pdt.DateTypeListItemId
                                                             AND li3.ListItem = 'HiredDate' ON pdt.PersonId = p.PersonId
             WHERE  ( EXISTS ( SELECT TOP 1 1
                               FROM   dbo.TfPersonDataAccessSel (@UserPersonId, 1) AS t
                                      INNER JOIN dbo.PersonNode AS pn ON pn.NodeId = t.NodeId
                               WHERE  p.PersonId = pn.PersonId )
                   OR EXISTS ( SELECT TOP 1 1
                               FROM   dbo.TfPersonDataAccessSel (@UserPersonId, 1) AS t
                                      INNER JOIN dbo.PersonDataAccess AS pn ON pn.NodeId = t.NodeId
                               WHERE  p.PersonId = pn.PersonId )));

--table valued function call like: 
 SELECT   DISTINCT t.PersonId ,
                                  t.[Name]
                FROM     dbo.TfPersonDataSel (@UserPersonId) AS t
                         INNER JOIN dbo.OfficeStaff AS os ON os.PersonId = t.PersonId
                ORDER BY t.[Name];

Answer 1

While it is possible for the compiler to remove some unnecessary parts of a query plan against a view or inline TFV, it can't remove everything.

Looking at your TVF, the following are going to cause issues:

• Inner joins cannot be removed by the compiler, unless they are from a child table to a parent table of a foreign key, the joined table is unique, and no columns are selected.
• Left joins cannot be removed, unless they are on either side of a foreign key, and the joined table is to a unique column.
• The EXISTS obviously need to stay, as do any other WHERE conditions.
  • It appears your two EXISTS clauses could be combined to some extent.
  • Also TOP 1 is not necessary in an EXISTS, it is implicit.
• We don't know what other views or TVFs being called are doing.

Other things not present in your TVF which can also not be optimized out:

• TOP except TOP (100) PERCENT
• Window or analytical functions.
• Aggregations or DISTINCT, unless it is against a unique column (after taking out all joins as above).
• UNION or other set operators, as they imply DISTINCT.
• UNION ALL, unless a particular part of the union can be definitely excluded due to later filters.

---

So if you want to keep as much code in a single place using the TVF, but still keep queries efficient when not querying all the data, i would say:

• Take out all joins that are many-to-many or one-to-many, as these cannot be removed.
• Ensure all joins are foreign-keyed tables, and that the FKs are trusted.
• Once you have done that, you should be able to remove DISTINCT on the outer query, as the final result will be unique anyway.
• Do not add any other problematic elements mentioned above.