比较回文问题的一些答案（仅限 10k+ 用户，因为我已经删除了答案），我得到了令人困惑的结果。

我提出了一个多语句、模式绑定的 TVF，我认为它比运行标准函数更快，事实也是如此。我还以为多语句 TVF 是“内联的”，尽管我在这方面是错误的，正如您将在下面看到的那样。这个问题是关于这两种 TVF 的性能差异。首先，您需要查看代码。

这是多语句 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

内联-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 <= L / 2
              AND SUBSTRING(S, N, 1) <> SUBSTRING(S, 1 + L - N, 1)
          )
          THEN 0
          ELSE 1
        END
    FROM
      (SELECT LTRIM(RTRIM(@Word)), LEN(@Word)) AS v (S, L)
);
GO

上面函数中的Numbers表定义为：

CREATE TABLE dbo.Numbers
(
    Number INT NOT NULL 
);

注意：numbers 表没有任何索引，也没有主键，包含 1,000,000 行。

测试台临时表：

IF OBJECT_ID('tempdb.dbo.#Words') IS NOT NULL
DROP TABLE #Words;
GO
CREATE TABLE #Words 
(
    Word VARCHAR(500) NOT NULL
);

INSERT INTO #Words(Word) 
SELECT o.name + REVERSE(w.name)
FROM sys.objects o
CROSS APPLY (
    SELECT o.name
    FROM sys.objects o
) w;

在我的测试系统上，上述INSERT结果导致 16,900 行被插入到#Words表中。

为了测试这两个变体，我SET STATISTICS IO, TIME ON;使用以下命令：

SELECT w.Word
    , p.IsPalindrome
FROM #Words w
    CROSS APPLY dbo.IsPalindrome(w.Word) p
ORDER BY w.Word;


SELECT w.Word
    , p.IsPalindrome
FROM #Words w
    CROSS APPLY dbo.InlineIsPalindrome(w.Word) p
ORDER BY w.Word;

我预计该InlineIsPalindrome版本会明显更快，但以下结果不支持该假设。

多语句 TVF：

Table ‘#A1CE04C3’. Scan count 16896, logical reads 16900, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#Words’. Scan count 1, logical reads 88, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1700 ms, elapsed time = 2022 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

内联 TVF：

Table ‘Numbers’. Scan count 1, logical reads 1272030, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#Words’. Scan count 1, logical reads 88, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 137874 ms, elapsed time = 139415 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

执行计划如下所示：

在这种情况下，为什么内联变体比多语句变体慢得多？

为了回应@AaronBertrand 的评论，我修改了dbo.InlineIsPalindrome函数以限制 CTE 返回的行以匹配输入词的长度：

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

正如@MartinSmith 所建议的那样，我已经在表中添加了一个主键和聚簇索引dbo.Numbers，这肯定会有所帮助并且更接近人们期望在生产环境中看到的内容。

现在重新运行上面的测试会产生以下统计数据：

CROSS APPLY dbo.IsPalindrome(w.Word) p:

(17424 row(s) affected)
Table ‘#B1104853’. Scan count 17420, logical reads 17424, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#Words’. Scan count 1, logical reads 90, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1763 ms, elapsed time = 2192 ms.

dbo.FunctionIsPalindrome(w.Word):

(17424 row(s) affected)
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#Words’. Scan count 1, logical reads 90, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 328 ms, elapsed time = 424 ms.

CROSS APPLY dbo.InlineIsPalindrome(w.Word) p:

(17424 row(s) affected)
Table ‘Numbers’. Scan count 1, logical reads 237100, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘Worktable’. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘#Words’. Scan count 1, logical reads 90, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 17737 ms, elapsed time = 17946 ms.

我正在 SQL Server 2012 SP3 v11.0.6020 开发人员版上对此进行测试。

这是我的数字表的定义，带有主键和聚集索引：

CREATE TABLE dbo.Numbers
(
    Number INT NOT NULL 
        CONSTRAINT PK_Numbers
        PRIMARY KEY CLUSTERED
);

;WITH n AS
(
    SELECT v.n 
    FROM (
        VALUES (1) 
            ,(2) 
            ,(3) 
            ,(4) 
            ,(5) 
            ,(6) 
            ,(7) 
            ,(8) 
            ,(9) 
            ,(10)
        ) v(n)
)
INSERT INTO dbo.Numbers(Number)
SELECT ROW_NUMBER() OVER (ORDER BY n1.n)
FROM n n1
    , n n2
    , n n3
    , n n4
    , n n5
    , n n6;