SQL-Ex blog

Если вы, как и я, пришли в восторг от введения STRING_AGG в лексику T-SQL, поскольку это может заменить весь старомодный код, то, вероятно, также быстро разочаровались по ряду причин.

• Требуется преобразовать элемент, который должен агрегироваться в MAX, чтобы избежать ошибок.
  • Результат агрегации STRING_AGG превысил предел 8000 байтов. Используйте типы LOB, чтобы избежать усечение результата.


• Упорядочение WITHIN GROUP неуклюже по сравнению с внешним ORDER BY (но эй, логическая обработка запросов ...).


• В функции нет поддержки DISTINCT, а внешний DISTINCT пытается сделать не тот DISTINCT (смотри выше).

Ну, и конечно нарушаются ограничения на встраивание UDF.

UDF не ссылаются на функцию STRING_AGG

Давайте посмотрим на все это вместе в одной функции.

CREATE OR ALTER FUNCTION 

    dbo.IsStringAgg (@UserId int)

RETURNS 

    nvarchar(max)

WITH 

    RETURNS NULL ON NULL INPUT, 

    SCHEMABINDING

AS

BEGIN

RETURN

(

    SELECT

        STRING_AGG

        (

            CONVERT

            (

                nvarchar(MAX),

                b2.Name

            ), 

            N', '

        )

        WITHIN GROUP 

        (

            ORDER BY 

                b2.Name

        )

    FROM

    (

        SELECT DISTINCT 

            b.Name

        FROM dbo.Badges AS b

        WHERE b.UserId = @UserId

    ) AS b2

);

END;

GO

Разве не красота, или нет? Но давайте задержимся на секунду.

XML > JSON

ОК, итак, давайте вернемся к той документации на UDF.

UDF не ссылаются на методы XML

Подобные сообщения читаются как ДЛЯ ВАС НЕТ XML (NOXML4U). Хотя на самом деле это означает все вещи .метод, как то value, node, query и т.д.

Поэтому, если вы последуете моему предыдущему совету по конкатенации строк, функция не будет являться встраиваемой.

SELECT

   x = 

   (

       SELECT 

           [text()] = 

               b.Name

       FROM dbo.Badges AS b

       WHERE b.Id = 100564

       FOR XML 

           PATH(''),

           TYPE

   ).value

     (

         './text()[1]', 

         'nvarchar(max)'

     );

Наличие .value препятствует встраиванию.

Поделиться функцией

Мы можем написать такую полуправильную функцию:

CREATE OR ALTER FUNCTION 

    dbo.NotStringAgg (@UserId int)

RETURNS 

    nvarchar(max)

WITH 

    RETURNS NULL ON NULL INPUT, 

    SCHEMABINDING

AS

BEGIN

RETURN

    STUFF

    (

        (

            SELECT

                N', ' + 

                b.Name

            FROM dbo.Badges AS b

            WHERE b.UserId = @UserId

            GROUP BY b.Name

            ORDER BY b.Name

            FOR XML PATH (N'')

        ), 

        1,

        2,

        N''

    );

END;

GO

Хорошо, теперь давайте вернем этот код обратно.

Если мы сравним планы выполнения этих двух функций, с XML становится встраиваемой, а с STRING_AGG - нет.

SELECT TOP (5000)

    u.DisplayName,

    ThanksForAllYourHardWork = 

        dbo.NotStringAgg(u.Id)

FROM dbo.Users AS u

ORDER BY u.Reputation DESC;

GO 



SELECT TOP (5000)

    u.DisplayName,

    ThanksForAllYourHardWork = 

        dbo.IsStringAgg(u.Id)

FROM dbo.Users AS u

ORDER BY u.Reputation DESC;

GO

Вот план для встраиваемой функции:



А это план для невстраиваемой функции:



Встраиваемая функция финиширует почти вдвое быстрей, хотя можно подумать, является ли разница в 400 мс значимой в этом случае.

Конечно, настоящая проблема - это когда скалярные UDF вызываются как часть более крупных запросов, где важен параллелизм и т.п.