Puzzling TVFunctions

2012/07/17

I had the opportunity to tune a highly-used function the other day, and was most puzzled by the difference between the old and the new function. This was on SQL Server 2005, and I was able to repro the issue on-server, but not on my SQL Server 2012 instance.

The old function specified the return table; it actually inserted the data into a table variable, and returned that:

-- =============================================
-- The working, but slow, function:
-- =============================================
CREATE FUNCTION test.myfunction_tableVar ( @Data NVARCHAR(800) )
RETURNS @tableVar TABLE ( data VARCHAR(800) )
AS 
    BEGIN    

        INSERT  @tableVar
                ( data )
        VALUES  ( LTRIM(RTRIM(@data)) ) ;
        RETURN    
    END    

GO

Note that these have been greatly simplified from the original functions, but still showed the same odd behavior.

Anyway, when I created a new function for testing, I neglected to use a table variable; instead, I just specified a TABLE return:

-- =============================================
-- The new, faster, wrong-resultset function:
-- =============================================
CREATE FUNCTION test.myfunction_table ( @Data NVARCHAR(800) )
RETURNS TABLE
AS
RETURN
SELECT LTRIM(RTRIM(@data)) AS [data] ;

I tested these thoroughly; a given input produced a single row of output, and the output of one function was IDENTICAL to the output of the other in all cases. I even joined the two functions on a.data = b.data, proving the identical output. However, when I used these in a query against live data, I got different resultsets from the query:

-- This returned 915 rows, as it should:
SELECT  idnumber
FROM    test.myTable
        INNER JOIN Speak.test.myfunction_tableVar('nom') f ON CHARINDEX(f.data,
                                                              [notes]) &gt; 0;
-- This returned 911 rows!
SELECT  idnumber
FROM    test.myTable
        INNER JOIN Speak.test.myfunction_table('nom') f ON CHARINDEX(f.data,
                                                              [notes]) &gt; 0;

So, help me out here. How can two functions with the EXACT same resultset, and used in the exact same way, turn out different resultsets when used in a query? Is this a bug, or a feature?

Happy days,
Jen McCown
www.MidnightDBA.com/Jen