SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Puzzling TVFunctions

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) )

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


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) )
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]) > 0;
-- This returned 911 rows!
SELECT  idnumber
FROM    test.myTable
        INNER JOIN Speak.test.myfunction_table('nom') f ON CHARINDEX(f.data,
                                                              [notes]) > 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

Share on TwitterShare on LinkedInShare on TumblrSubmit to StumbleUponSave on DeliciousDigg ThisSubmit to reddit


Leave a comment on the original post [www.midnightdba.com, opens in a new window]

Loading comments...