Multi-statement TVF vs Inline TVF Performance

Comparing some of the answers on the Palindrome question(10k+ users only, since I've deleted the answer), I'm getting confusing results.

I proposed a multi-statement, schema-bound TVF which I thought would be faster than running a standard function, which it is. I was also under the impression that the multi-statement TVF would be "inlined", although I am wrong on that count, as you'll see below. This question is about the performance difference of those two styles of TVF. First, you'll need to see the code.

Here is the multi-statement TVF:

IF OBJECT_ID('dbo.IsPalindrome') IS NOT NULL
DROP FUNCTION dbo.IsPalindrome;
GO
CREATE FUNCTION dbo.IsPalindrome
(
    @Word NVARCHAR(500)
) 
RETURNS @t TABLE
(
    IsPalindrome BIT NOT NULL
)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @IsPalindrome BIT;
    DECLARE @LeftChunk NVARCHAR(250);
    DECLARE @RightChunk NVARCHAR(250);
    DECLARE @StrLen INT;
    DECLARE @Pos INT;
    SET @RightChunk = '';
    SET @IsPalindrome = 0;
    SET @StrLen = LEN(@Word) / 2;
    IF @StrLen % 2 = 1 SET @StrLen = @StrLen - 1;
    SET @Pos = LEN(@Word);
    SET @LeftChunk = LEFT(@Word, @StrLen);
    WHILE @Pos > (LEN(@Word) - @StrLen)
    BEGIN
        SET @RightChunk = @RightChunk + SUBSTRING(@Word, @Pos, 1)
        SET @Pos = @Pos - 1;
    END
    IF @LeftChunk = @RightChunk SET @IsPalindrome = 1;
    INSERT INTO @t VALUES (@IsPalindrome);
    RETURN
END
GO

The inline-TVF:

```
IF OBJECT_ID('dbo.InlineIsPalindrome') IS NOT NULL
DROP FUNCTION dbo.InlineIsPalindrome;
GO
CREATE FUNCTION dbo.InlineIsPalindrome
(
@Word NVARCHAR(500)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
WITH Nums AS
(
SELECT
N = number
FROM
dbo.Numbers
)
SELECT
IsPalindrome =
CASE
WHEN EXISTS
(
SELECT N
FROM Nums
WHERE N SUBSTRING(S, 1 + L - N, 1)
)
THEN 0
ELSE 1
END
FROM
(SELECT LTRIM(RTRIM(@Word)

Your numbers table is a heap and is potentially being fully scanned each time.

Add a clustered primary key on Number and try the following with a forceseek hint to get the desired seek.

As far as I can tell this hint is needed as SQL Server just estimates that 27% of the table will match the predicate (30% for the ``). And therefore that it will only have to read 3-4 rows before finding one that matches and it can exit the semi join. So the scan option is costed very cheaply. But in fact if any palindromes do exist then it will have to read the whole table so this is not a good plan.

CREATE FUNCTION dbo.InlineIsPalindrome
(
    @Word NVARCHAR(500)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN (
    WITH Nums AS
    (
      SELECT
        N = number
      FROM
        dbo.Numbers WITH(FORCESEEK)
    )
    SELECT
      IsPalindrome =
        CASE
          WHEN EXISTS
          (
            SELECT N
            FROM Nums
            WHERE N  SUBSTRING(S, 1 + L - N, 1)
          )
          THEN 0
          ELSE 1
        END
    FROM
      (SELECT LTRIM(RTRIM(@Word)), LEN(@Word)) AS v (S, L)
);
GO

With those changes in place it flies for me (takes 228ms)

StackExchange Database Administrators Q#137775, answer score: 12