performance - 这里可以避免使用 CURSOR 吗？

Question

我有一个标量 UDF，它负责返回其中包含唯一单词的 NVARCHAR(MAX)。别问为什么。 :)

示例输入:“披萨菠萝萨拉米披萨奶酪奶酪”

示例输出:“披萨菠萝萨拉米奶酪”。

这是 UDF:

ALTER FUNCTION [dbo].[ToUniqueString]
(
    @NonUniqueString NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @ReturnValue NVARCHAR(MAX)

    -- Split the string by spaces.
    DECLARE @Words TABLE (Word NVARCHAR(MAX))
    INSERT INTO @Words
    SELECT Word FROM dbo.SplitText(@NonUniqueString , ' ')

    -- Cursor through the records, creating a unique string.
    DECLARE @CurrentWord NVARCHAR(MAX)
    DECLARE @UniqueString NVARCHAR(MAX) = ''

    DECLARE WordCursor CURSOR FOR SELECT DISTINCT Word FROM @Words
    OPEN WordCursor
    FETCH NEXT FROM WordCursor INTO @CurrentWord
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @UniqueString = @UniqueString + ' ' + @CurrentWord

        FETCH NEXT FROM WordCursor INTO @CurrentWord
    END
    CLOSE WordCursor
    DEALLOCATE WordCursor

    RETURN RTRIM(LTRIM(@UniqueString ))
END

没有光标可以完成吗？ WHILE 循环效率更高吗？ FOR XML 怎么样？

只是寻找最有效的方法来实现需求。

Answer 1

可以用FOR XML替换光标来完成:

ALTER FUNCTION [dbo].[ToUniqueString] 
( 
    @NonUniqueString NVARCHAR(MAX) 
) 
RETURNS NVARCHAR(MAX) 
AS 
BEGIN 
    DECLARE @ReturnValue NVARCHAR(MAX) 

    -- Split the string by spaces. 
    DECLARE @Words TABLE (Word NVARCHAR(MAX)) 
    INSERT INTO @Words 
    SELECT Word FROM dbo.SplitText(@NonUniqueString , ' ') 

    DECLARE @UniqueString NVARCHAR(MAX) = '' 

    set @UniqueString = 
    stuff( 
        (SELECT Distinct  ' ' + Word
         FROM @Words 
         for xml path('')) 
    , 1, 1, '')   

    RETURN @UniqueString
END