Mixing indexed and calculated fields in a table-valued function

Question

I work with SQL Server 2008, but can use a later version if it would matter.

I have 2 tables with pretty similar data about some people but in different formats (no intersections between these 2 sets of people).

Table 1:    
int personID    
bit IsOldPerson  //this field is indexed

Table 2:
int PersonID
int Age

I want to have a combined view that has the same structure as the Table 1. So I write the following script (a simplified version):

CREATE FUNCTION CombinedView(@date date)
RETURNS TABLE
AS
RETURN        
    select personID as PID, IsOldPerson as IOP
    from Table1

    union all

    select personID as PID, dbo.CheckIfOld(Age,@date) as IOP
    from Table2
GO

The function "CheckIfOld" returns yes/no depending on the input age at the date @date.

So I have 2 questions here:

A. if I try select * from CombinedView(TODAY) where IOP=true, whether the SQL Server will do the following separately: 1) for the Table 1 use the index for the field IsOldPerson and do a "clever" index-based selection of results; 2) for the Table 2 calculate CheckIfOld for all the rows and during the calculation pick up or rejecting rows on the row-by-row basis ?

B. how can I check the execution plan in this particular case to understand whether my guess in the question (A) is correct or not?

Any help is greatly appreciated! Thanks!

Your guess looks pretty accurate to me. Assuming a covering index on `Table1(IsOldPerson) INCLUDE (personID)` I would expect the plan to show a concatenation operator with an index seek on table1 and a scan with filter on table2. See [How do I obtain a Query Execution Plan?](http://stackoverflow.com/questions/7359702/how-do-i-obtain-a-query-execution-plan/7359705#7359705) — Martin Smith, Oct 27 '13 at 17:32
@Martin Smith : Thank's a lot for the comment! Sorry, I just realized that I have to amend the nature of the question and use rather table-valued function than view. — user2925546, Oct 27 '13 at 17:36
Though now you've edited it to a function rather than a view this depends if it is an inline TVF or a multi statement TVF. The function you have shown is invalid as it is neither. — Martin Smith, Oct 27 '13 at 17:36
@Martin Smith : Thanks for the remark! I assumed inline TVF because I'm afraid that MSTVF would take too much time for inserting the data in an intermediary table. So now I've changed the script to exactly ITVF. — user2925546, Oct 27 '13 at 17:46

score 0 · Answer 1 · answered Mar 06 '14 at 19:46

Yes, if the query isn't too complex, the query optimizer should "see through" the view into its constituent UNION-ed SELECT statements, evaluate them separately, and concatenate the results. If there is an index on Table1, it should be able to use it. I tested this using tables we had and the same function concepts you presented. I reviewed the query plans of the raw SELECT to Table1 and the SELECT to the inline table-valued function with the UNION and the portion of the query plan relevant to Table1 was the same-- and it used the index.

Now if performance is a concern, I suggest you do one of two things:

If (a) Table2 is read-heavy rather than write-heavy, (b) you have the space, and (c) you can write CheckIfOld as a single CASE statement (as its name and context in your question implies), then you should consider creating a persisted calculated field in Table2 with the calculation from IsOldPerson and applying an index to it.
If Table2 is write-heavy, or you have no space for additional fields, you should at least consider converting CheckIfOld into an inline function. You will likely reap performance gains, depending on how it is used. In your case, it would be used like this:

select personID as PID, IOP.IsOldPerson from Table2 CROSS APPLY dbo.CheckIfOld(Age,@date) AS IOP

Mixing indexed and calculated fields in a table-valued function

1 Answers1