В этой статье мы рассмотрим, как улучшить функцию значения таблицы в SQL Server, которая возвращает набор записей CDC (Change Data Capture). Текущая реализация выводит измененные данные строка за строкой, но нам нужно выводить их столбец за столбцом.

Давайте рассмотрим пример, где у нас есть таблица с именем “auth.Account” с включенным CDC для полей “Email” и “Password”. Мы уже включили CDC с помощью следующей команды:

EXEC sys.sp_cdc_enable_table @source_schema = N'auth', @source_name = N'Account', @captured_column_list = N'Email, Password', @supports_net_changes = 1;

Теперь нашей целью является изменение функции значения таблицы для возврата набора записей со следующими столбцами:

ID, CDC_OPERATION, LSN, CurrentEmail, PreviousEmail, CurrentPassword, PreviousPassword

Давайте посмотрим на текущую реализацию функции:

ALTER FUNCTION [auth].[ChangedAuthDetails] (
    @FromLsn BINARY(10)
)
RETURNS @User TABLE (
    UserID INT,
    CDC_OPERATION CHAR(1),
    LSN BINARY(10),
    CurrentEmail NVARCHAR(255),
    PreviousEmail NVARCHAR(255),
    CurrentPassword NVARCHAR(32),
    PreviousPassword NVARCHAR(32)
)
AS
BEGIN
    DECLARE @ToLsn BINARY(10)
    
    IF (@FromLsn IS NULL)
        SELECT @FromLsn = sys.fn_cdc_get_min_lsn(N'auth_account')
    ELSE
        SELECT @FromLsn = sys.fn_cdc_increment_lsn(@FromLsn)
    
    SELECT @ToLsn = sys.fn_cdc_get_max_lsn()
    
    IF (@FromLsn = sys.fn_cdc_increment_lsn(@ToLsn))
        RETURN
    
    -- Запрос для изменения данных
    INSERT INTO @User
    SELECT
        a.UserID,
        'U' AS [__$operation],
        a.[__$start_lsn],
        A.CurrentEmail,
        a.PreviousEmail,
        b.CurrentPassword,
        b.PreviousPassword
    FROM
        (
            SELECT
                Email.[__$start_lsn],
                Email.ID AS [UserID],
                Email.[3] AS [PreviousEmail],
                Email.[4] AS [CurrentEmail]
            FROM
                (
                    SELECT
                        [__$start_lsn],
                        ID,
                        Email,
                        [__$operation]
                    FROM
                        cdc.fn_cdc_get_all_changes_auth_account(@FromLsn, @ToLsn, 'all update old')
                    WHERE
                        [__$operation] IN (3, 4)
                ) P
            PIVOT
            (
                MAX(Email) FOR [__$operation] IN ([3], [4])
            ) AS Email
        ) A
    CROSS APPLY
        (
            SELECT
                [password].[__$start_lsn],
                [Password].[3] AS [PreviousPassword],
                [Password].[4] AS [CurrentPassword]
            FROM
                (
                    SELECT
                        [__$start_lsn],
                        ID,
                        [Password],
                        [__$operation]
                    FROM
                        cdc.fn_cdc_get_all_changes_auth_account(@FromLsn, @ToLsn, 'all update old')
                    WHERE
                        [__$operation] IN (3, 4)
                ) P
            PIVOT
            (
                MAX([Password]) FOR [__$operation] IN ([3], [4])
            ) AS [Password]
        ) B
    WHERE
        A.__$start_lsn = B.__$start_lsn
    
    UNION
    
    SELECT
        [ID] AS [UserID],
        CASE __$operation
            WHEN 1 THEN 'D'
            WHEN 2 THEN 'I'
            ELSE NULL
        END AS CDC_OPERATION,
        [__$start_lsn],
        CASE [__$operation]
            WHEN 1 THEN NULL
            WHEN 2 THEN Email
        END AS [CurrentEmail],
        CASE [__$operation]
            WHEN 1 THEN Email
            WHEN 2 THEN NULL
        END AS [PreviousEmail],
        CASE [__$operation]
            WHEN 1 THEN NULL
            WHEN 2 THEN [Password]
        END AS [CurrentPassword],
        CASE [__$operation]
            WHEN 1 THEN [Password]
            WHEN 2 THEN NULL
        END AS [PreviousPassword]
    FROM
        cdc.fn_cdc_get_all_changes_auth_account(@FromLsn, @ToLsn, 'all update old')
    WHERE
        [__$operation] < 3
    
    RETURN
END

Как видите, текущая реализация немного запутанная и может быть улучшена. Вот несколько идей, как ее улучшить:

1. Разделите функцию на более мелкие, более управляемые части: Вместо одной монолитной функции рассмотрите возможность разделения ее на более мелкие функции или хранимые процедуры. Это сделает код более модульным и позволит легче понять и поддерживать его.
2. Оптимизируйте запросы: Ищите возможности для оптимизации запросов, используемых в функции. Это может включать добавление соответствующих индексов, переписывание запросов для использования более эффективных соединений или подзапросов или использование подсказок запроса для улучшения производительности.
3. Используйте понятные имена переменных и столбцов: В текущей реализации используются общие имена, такие как “a”, “b” и “P” для переменных и столбцов. Рассмотрите возможность использования более понятных имен, которые точно описывают назначение каждой переменной или столбца.
4. Добавьте комментарии и документацию: Добавьте комментарии в код, чтобы объяснить цель и функциональность каждого раздела. Это упростит понимание и изменение кода другим разработчикам в будущем.
5. Рассмотрите использование другого подхода: В зависимости от ваших конкретных требований, вы можете рассмотреть возможность использования другого подхода. Например, вы можете использовать другую структуру данных или шаблон проектирования для достижения желаемого результата.

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

Спасибо за прочтение этой статьи. Мы надеемся, что она поможет вам улучшить функцию значения таблицы в SQL Server.