SQL inline table function with table value parameter hangs

Question

I wrote a inline table function which works fine on my machine (SQL 08 R2 on Win 7), but in any other environment (SQL 08 R2 on Win 2003 Server) just hangs, even under the lightest loads. There are no differences between database or server settings.

ALTER FUNCTION dbo.Foo
(
    @tableParam dbo.TableType READONLY
)
RETURNS TABLE AS RETURN
    WITH t AS 
        (SELECT 
                rc.ClientId, rc.Type,
                f.Id AS NurseId
            FROM dbo.PracticeNurses AS pf WITH (NOLOCK)
                INNER JOIN @tableParam AS rc ON rc.RowIds = pf.Id
                LEFT JOIN dbo.Nurses AS f WITH (NOLOCK) ON f.Id = pf.NurseId)
    SELECT DISTINCT '[dbo].[Nurses]' AS T, CAST(t.NurseId AS VARCHAR(300)) AS I, ClientId, Type FROM t

Changing it to a multi-statement function which performs exactly the same operation solves the problem instantly - works in all environments:

ALTER FUNCTION dbo.Foo
(
    @rowsClients dbo.TableType READONLY
)
RETURNS @result TABLE
(
    T VARCHAR(300),
    I VARCHAR(300), 
    ClientId INT,
    Type TINYINT
)
AS
BEGIN
    WITH t AS 
        (SELECT 
                rc.ClientId, rc.Type,
                f.Id AS NurseId
            FROM dbo.PracticeNurses AS pf WITH (NOLOCK)
                INNER JOIN @tableParam AS rc ON rc.RowIds = pf.Id
                LEFT JOIN dbo.Nurses AS f WITH (NOLOCK) ON f.Id = pf.NurseId)
    INSERT INTO @result
    SELECT DISTINCT '[dbo].[Nurses]' AS T, CAST(t.NurseId AS VARCHAR(300)) AS I, ClientId, Type FROM t

    RETURN;
END

Table type defined as follows:

CREATE TYPE dbo.TableType AS TABLE
(
    RowIds VARCHAR(300),
    Type TINYINT,
    ClientId INT
);

Calling code is straight forward:

DECLARE @t dbo.TableType;
INSERT INTO @t VALUES () ...; -- a few rows, less than 10. same issue without an insert
SELECT * FROM dbo.foo(@t); -- just hangs and does nothing

I changed it to multi statement for now, but would obviously prefer to use the inline version for performance reasons. I tried running sp_WhoIsActive continously and it shows a strange phenomena: the "TSQL" column alternates between blank and the text of the calling query. When querying dm_tran_locks, it's also strange, shows locking these objects: sysrscols, sysrowsets, sysallocunits, sysprivs, sysschobjs, syscolpars, sysxprops, sysidxstats, sysiscols, sysaudacts, sysobjvalues, syssingleobjrefs, sysmultiobjrefs, sysobjkeycrypts, syssoftobjrefs, foo (name of function, sometimes shows up twice), Nurses (one of the tables), TT_TableType_2085175E (some kind of index belonging to the TVP); but not all the time - they come and go as I rerun the dm_tran_locks query, sometimes dropping to 2 of the above as many as all of the above. Meanwhile, the query window continues hanging. It doesn't overload the server in anyway, I can easily cancel by calling SqlCommand.Cancel() and by kill -ing the session on the SQL side.

Needless to say, the same query as a multi-statement or run directly returns immediately in all environments.

SQL inline table function with table value parameter hangs

0 Answers0