SQL Server 2019 infinitely recompiles inline table valued function that does full text search with Parameterization = Forced

Question

I have hit a problem with SQL Server that results in it infinitely recompiling a function.

To reproduce, create a new database with the option Parameterization = Forced or execute the following on an existing DB:

ALTER DATABASE [DatabaseName] SET PARAMETERIZATION FORCED WITH NO_WAIT

Then execute the following script:

CREATE TABLE dbo.TestTable(
    ID int IDENTITY(1,1) NOT NULL,
    FullTextField varchar(100) NULL,
    CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED
    (ID ASC)
)
GO

IF NOT EXISTS(SELECT 1 FROM sysfulltextcatalogs WHERE name = 'FullTextCat')
    CREATE FULLTEXT CATALOG FullTextCat;
GO
    
CREATE FULLTEXT INDEX ON dbo.TestTable (FullTextField)  KEY INDEX PK_TestTable
    ON FullTextCat
    WITH
    CHANGE_TRACKING AUTO
GO

CREATE OR ALTER FUNCTION dbo.fn_TestFullTextSearch(@Filter VARCHAR(8000))
RETURNS TABLE
AS
    RETURN SELECT
        ID,
        FullTextField
        FROM dbo.TestTable
        WHERE CONTAINS(FullTextField, @Filter)
GO

SELECT * FROM dbo.fn_TestFullTextSearch('"a*"')

The query will never return. Running SQL Profiler to monitor SP:CacheInsert and SP:CacheRemove will show SQL server is doing this endlessly and the SQL logs will show countless "A possible infinite recompile was detected for SQLHANDLE" messages.

Setting the Parameterization = Simple works around the issue but we need this to be set to Forced for other reasons.

Has anyone come across this issue before and/or have a suggested solution?

Thanks,

Chuck

Answer 1

While I still experience the problem with the original code I provided, by following @Martin's approach of explicitly parameterizing the call to the function:

EXEC sys.sp_executesql N'SELECT * FROM dbo.fn_TestFullTextSearch(@Filter)', N'@Filter VARCHAR(4)', @Filter = '"a*"'

I have been able to successfully work around the problem.