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