フィルター関数を使用して移行する行を選択する (Stretch Database)

適用対象: SQL Server 2016 (13.x) 以降 - Windows のみ

重要

拡張データベースは、SQL Server 2022 (16.x) および Azure SQL Database では非推奨になります。 この機能は、データベース エンジンの将来のバージョンで削除される予定です。 新規の開発作業ではこの機能を使用しないようにし、現在この機能を使用しているアプリケーションは修正することを検討してください。

コールド データを別のテーブルに保存している場合、そのテーブル全体を移行するように Stretch Database を構成できます。 一方、テーブルにホット データとコールド データの両方が含まれている場合は、移行する行を選択するフィルター述語を指定できます。 フィルター述語はインライン テーブル値関数です。 この記事では、移行する行を選択するインライン テーブル値関数を作成する方法について説明します。

重要

指定したフィルター関数のパフォーマンスが低いと、データ移行のパフォーマンスも低くなります。 Stretch Database では、CROSS APPLY 演算子を使用してテーブルにフィルター関数を適用します。

フィルター関数を指定しない場合、テーブル全体が移行されます。

データベースのストレッチの有効化ウィザードを実行すると、テーブル全体を移行することも、ウィザードでフィルター関数を指定することもできます。 移行する行を選択するために別の種類のフィルター関数を使用する場合、次のいずれかの操作を行います。

  • ウィザードを終了し、ALTER TABLE ステートメントを実行してテーブルの Stretch を有効にして、フィルター関数を指定します。

  • ウィザードを終了した後、ALTER TABLE ステートメントを実行してフィルター関数を指定します。

関数を追加するための ALTER TABLE 構文については、この記事で後述します。

フィルター関数の基本的な要件

Stretch Database のフィルター述語に必要なインライン テーブル値関数は次の例のようになります。

CREATE FUNCTION dbo.fn_stretchpredicate (
    @column1 datatype1,
    @column2 datatype2 /*[, ...n]*/
    )
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN

SELECT 1 AS is_eligible
WHERE someCol = @column1 /* replace with an actual predicate */

関数のパラメーターは、テーブルの列の識別子である必要があります。

フィルター関数で使用される列が削除または変更されるのを防ぐために、スキーマ バインドが必要となります。

戻り値

関数から空ではない結果が返された場合、その行は移行の対象になります。 それ以外の場合 (結果が返されない場合)、その行は移行の対象にはなりません。

条件

<predicate> は、1 つの条件で構成される場合もあれば、AND 論理演算子で結合された複数の条件で構成される場合もあります。

<predicate> ::= <condition> [ AND <condition> ] [ ...n ]

各条件は、1 つのプリミティブ条件で構成される場合もあれば、OR 論理演算子で結合された複数のプリミティブ条件で構成される場合もあります。

<condition> ::= <primitive_condition> [ OR <primitive_condition> ] [ ...n ]

プリミティブ条件

プリミティブ条件では、次のいずれかの比較を実行できます。

<primitive_condition> ::=
{
<function_parameter> <comparison_operator> constant
| <function_parameter> { IS NULL | IS NOT NULL }
| <function_parameter> IN ( constant [ ,...n ] )
}
  • 関数パラメーターと定数式を比較します。 たとえば、「 @column1 < 1000 」のように入力します。

    次の例では、date 列の値が 2016 年 1 月 1 日より前かどうかを確認します。

    CREATE FUNCTION dbo.fn_stretchpredicate (@column1 DATETIME)
    RETURNS TABLE WITH SCHEMABINDING
    AS
    RETURN
    
    SELECT 1 AS is_eligible
    WHERE @column1 < CONVERT(DATETIME, '1/1/2016', 101)
    GO
    
    ALTER TABLE stretch_table_name SET (
        REMOTE_DATA_ARCHIVE = ON (
            FILTER_PREDICATE = dbo.fn_stretchpredicate(DATE),
            MIGRATION_STATE = OUTBOUND
    ));
    
  • IS NULL または IS NOT NULL 演算子を関数パラメーターに適用します。

  • IN 演算子を使用して、関数パラメーターと定数値のリストを比較します。

    次の例では、shipment_status 列の値が IN (N'Completed', N'Returned', N'Cancelled') であるかどうかを確認します。

    CREATE FUNCTION dbo.fn_stretchpredicate (@column1 NVARCHAR(15))
    RETURNS TABLE WITH SCHEMABINDING
    AS
    RETURN
    
    SELECT 1 AS is_eligible
    WHERE @column1 IN (
        N'Completed',
        N'Returned',
        N'Cancelled'
    )
    GO
    
    ALTER TABLE table1 SET (
        REMOTE_DATA_ARCHIVE = ON (
      	  FILTER_PREDICATE = dbo.fn_stretchpredicate(shipment_status),
      	  MIGRATION_STATE = OUTBOUND
    ));
    

比較演算子

次の比較演算子がサポートされています。

<, <=, >, >=, =, <>, !=, !<, !>

<comparison_operator> ::= { < | <= | > | >= | = | <> | != | !< | !> }

定数式

関数を定義するときに、フィルター関数で使用する定数を、評価できる決定論的な式にすることができます。 定数式には以下を含めることができます。

  • リテラル。 たとえば、「 N'abc', 123 」のように入力します。

  • 代数式。 たとえば、「 123 + 456 」のように入力します。

  • 決定論的関数。 たとえば、「 SQRT(900) 」のように入力します。

  • CAST または CONVERT を使用した決定論的変換。 たとえば、「 CONVERT(datetime, '1/1/2016', 101) 」のように入力します。

その他の式

BETWEEN 演算子と NOT BETWEEN 演算子を同等の AND 式と OR 式に置き換えた後、結果的に作成される関数がここで説明するルールに準拠する場合は、BETWEEN 演算子と NOT BETWEEN 演算子を使用できます。

サブクエリや非決定論的関数 (RAND() や GETDATE() など) は使用できません。

フィルター関数をテーブルに追加する

ALTER TABLE ステートメントを実行し、 FILTER_PREDICATE パラメーターの値として既存のインライン テーブル値関数を指定して、テーブルにフィルター関数を追加します。 たとえば、次のように入力します。

ALTER TABLE stretch_table_name SET (
	REMOTE_DATA_ARCHIVE = ON (
		FILTER_PREDICATE = dbo.fn_stretchpredicate(column1, column2),
		MIGRATION_STATE = OUTBOUND /* replace OUTBOUND in this example, with the actual, desired migration state */
));

関数を述語としてテーブルにバインドすると、次のようになります。

  • 次回のデータ移行時に、関数から空ではない値が返された行だけが移行されます。

  • 関数で使用される列はスキーマ バインドされています。 テーブルで関数がフィルター述語として使用されている限り、これらの列を変更することはできません。

テーブルで関数がフィルター述語として使用されている限り、インライン テーブル値関数を削除することはできません。

フィルター関数のパフォーマンスを向上させるには、関数が使用する列にインデックスを作成します。

列名をフィルター関数に渡す

テーブルにフィルター関数を割り当てるときは、1 部構成の名前を使用してフィルター関数に渡される列名を指定します。 列名を渡すときに、3 部構成の名前を指定すると、Stretch 対応テーブルに対する後続のクエリが失敗します。

たとえば、次の例のように 3 部構成の列名を指定すると、ステートメントは正常に実行されますが、テーブルに対する後続のクエリは失敗します。

ALTER TABLE SensorTelemetry SET (
	REMOTE_DATA_ARCHIVE = ON (
		FILTER_PREDICATE = dbo.fn_stretchpredicate(dbo.SensorTelemetry.ScanDate),
		MIGRATION_STATE = OUTBOUND
));

代わりに、次の例で示すように、1 部構成の列名を持つフィルター関数を指定します。

ALTER TABLE SensorTelemetry SET (
	REMOTE_DATA_ARCHIVE = ON (
		FILTER_PREDICATE=dbo.fn_stretchpredicate(ScanDate),
		MIGRATION_STATE = OUTBOUND
));

ウィザードの実行後、フィルター関数を追加する

データベースのストレッチの有効化 ウィザードで作成できない関数を使用したい場合は、ウィザードを終了してから ALTER TABLE ステートメントを実行して関数を指定できます。 ただし、関数を適用する前に、既に進行中のデータ移行を停止し、移行されたデータを戻す必要があります。 (これが必要な理由については、「 既存のフィルター関数の置き換え」を参照してください)。

  1. 移行の方向を反転し、既に移行されたデータを戻します。 この操作は開始すると取り消すことはできません。 また、送信データ転送 (エグレス) のための Azure のコストも発生します。 詳細については、「 Data Transfers (データ転送) の料金詳細」を参照してください。

    ALTER TABLE [<table name>] SET (
        REMOTE_DATA_ARCHIVE (
            MIGRATION_STATE = INBOUND
    ));
    
  2. 移行が完了するまで待ちます。 状態は、SQL Server Management Studio から Stretch Database モニター で確認することも、 sys.dm_db_rda_migration_status ビューでクエリを実行して確認することもできます。 詳細については、「 データ移行の監視とトラブルシューティング 」または「 sys.dm_db_rda_migration_status」を参照してください。

  3. テーブルに適用するフィルター関数を作成します。

  4. 関数をテーブルに追加し、Azure へのデータ移行を再開します。

    ALTER TABLE [<table name>] SET (
        REMOTE_DATA_ARCHIVE (
            FILTER_PREDICATE = dbo.predicateFunction(col1),
            /* replace predicateFunction and col1 with the actual function call */
            MIGRATION_STATE = OUTBOUND
    ));
    

日付による行のフィルター

次の例では、 date 列に 2016 年 1 月 1 日より前の値が含まれている行を移行します。

-- Filter by date
--
CREATE FUNCTION dbo.fn_stretch_by_date (@date DATETIME2)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN

SELECT 1 AS is_eligible
WHERE @date < CONVERT(DATETIME2, '1/1/2016', 101)
GO

status 列の値による行のフィルター

次の例では、 status 列に指定した値のいずれかが含まれている行を移行します。

-- Filter by status column
--
CREATE FUNCTION dbo.fn_stretch_by_status (@status NVARCHAR(128))
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN

SELECT 1 AS is_eligible
WHERE @status IN (
	N'Completed',
	N'Returned',
	N'Cancelled'
)
GO

スライディング ウィンドウを使用した行のフィルター

スライディング ウィンドウを使用して行をフィルター処理する場合は、フィルター関数の以下の要件を考慮してください。

  • 関数は決定論的である必要があります。 そのため、時間の経過に伴って、スライディング ウィンドウを自動的に再計算する関数を作成することはできません。

  • 関数ではスキーマ バインドを使用します。 そのため、ALTER FUNCTION を呼び出してスライディング ウィンドウを移動することで、"配置済みの" 関数を毎日更新することはできません。

systemEndTime 列に 2016 年 1 月 1 日より前の値が含まれている行を移行する、次の例のようなフィルター関数から始めます。

CREATE FUNCTION dbo.fn_StretchBySystemEndTime20160101 (@systemEndTime DATETIME2)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN

SELECT 1 AS is_eligible
WHERE @systemEndTime < CONVERT(DATETIME2, '2016-01-01T00:00:00', 101);

フィルター関数をテーブルに適用します。

ALTER TABLE [<table name>] SET (
	REMOTE_DATA_ARCHIVE = ON (
		FILTER_PREDICATE = dbo.fn_StretchBySystemEndTime20160101(ValidTo),
		MIGRATION_STATE = OUTBOUND
));

スライディング ウィンドウを更新する場合は、次の手順に従います。

  1. 新しいスライディング ウィンドウを指定する新しい関数を作成します。 次の例では、2016 年 1 月 1 日ではなく、2016 年 1 月 2日より前の日付を選択します。

  2. 次の例に示すように、 ALTER TABLEを呼び出して、以前のフィルター関数を新しい関数で置き換えます。

  3. 必要に応じて、 DROP FUNCTIONを呼び出して不要になった以前のフィルター関数を削除します。 (この手順は例には含まれていません)。

BEGIN TRANSACTION
GO

/*(1) Create new predicate function definition */
CREATE FUNCTION dbo.fn_StretchBySystemEndTime20160102 (@systemEndTime DATETIME2)
RETURNS TABLE
    WITH SCHEMABINDING
AS
RETURN

SELECT 1 AS is_eligible
WHERE @systemEndTime < CONVERT(DATETIME2, '2016-01-02T00:00:00', 101)
GO

/*(2) Set the new function as the filter predicate */
ALTER TABLE [<table name>] SET (
	REMOTE_DATA_ARCHIVE = ON (
		FILTER_PREDICATE = dbo.fn_StretchBySystemEndTime20160102(ValidTo),
		MIGRATION_STATE = OUTBOUND
));

COMMIT;

有効なフィルター関数のその他の例

  • 次の例では、AND 論理演算子を使用して 2 つのプリミティブ条件を結合しています。

    CREATE FUNCTION dbo.fn_stretchpredicate (
        @column1 DATETIME,
        @column2 NVARCHAR(15)
    )
    RETURNS TABLE
        WITH SCHEMABINDING
    AS
    RETURN
    
    SELECT 1 AS is_eligible
    WHERE @column1 < N'20150101'
        AND @column2 IN (
            N'Completed',
            N'Returned',
            N'Cancelled'
        );
    GO
    
    ALTER TABLE table1 SET (
        REMOTE_DATA_ARCHIVE = ON (
      	  FILTER_PREDICATE = dbo.fn_stretchpredicate(DATE, shipment_status),
      	  MIGRATION_STATE = OUTBOUND
    ));
    GO
    
  • 次の例では、複数の条件と CONVERT による決定論的変換を使用しています。

    CREATE FUNCTION dbo.fn_stretchpredicate_example1 (
        @column1 DATETIME,
        @column2 INT,
        @column3 NVARCHAR
    )
    RETURNS TABLE
        WITH SCHEMABINDING
    AS
    RETURN
    
    SELECT 1 AS is_eligible
    WHERE @column1 < CONVERT(DATETIME, '1/1/2015', 101)
        AND (
            @column2 < - 100
            OR @column2 > 100
            OR @column2 IS NULL
        )
        AND @column3 IN (
            N'Completed',
            N'Returned',
            N'Cancelled'
        );
    GO
    
  • 次の例では、算術演算子と関数を使用しています。

    CREATE FUNCTION dbo.fn_stretchpredicate_example2 (@column1 FLOAT)
    RETURNS TABLE
    WITH SCHEMABINDING
    AS
    RETURN SELECT 1 AS is_eligible
           WHERE @column1 < SQRT(400) + 10;
    GO
    
  • 次の例では、BETWEEN 演算子と NOT BETWEEN 演算子を使用しています。 BETWEEN 演算子と NOT BETWEEN 演算子を同等の AND 式と OR 式に置き換えた後、結果的に作成される関数がここで説明するルールに準拠するため、この使用方法は有効です。

    CREATE FUNCTION dbo.fn_stretchpredicate_example3 (
        @column1 INT,
        @column2 INT
    )
    RETURNS TABLE
        WITH SCHEMABINDING
    AS
    RETURN
    
    SELECT 1 AS is_eligible
    WHERE @column1 BETWEEN 0 AND 100
        AND (
            @column2 NOT BETWEEN 200 AND 300
            OR @column1 = 50
        );
    GO
    

    BETWEEN 演算子と NOT BETWEEN 演算子を同等の AND 式と OR 式に置き換えた結果、置換前の関数は置換後の関数と等しくなっています。

    CREATE FUNCTION dbo.fn_stretchpredicate_example4 (
        @column1 INT,
        @column2 INT
    )
    RETURNS TABLE
        WITH SCHEMABINDING
    AS
    RETURN
    
    SELECT 1 AS is_eligible
    WHERE @column1 >= 0
        AND @column1 <= 100
        AND (
            @column2 < 200
            OR @column2 > 300
            OR @column1 = 50
        );
    GO
    

無効なフィルター関数の例

  • 非決定論的変換が含まれているため、次の関数は無効です。

    CREATE FUNCTION dbo.fn_example5 (@column1 DATETIME)
    RETURNS TABLE WITH SCHEMABINDING
    AS
    RETURN
    
    SELECT 1 AS is_eligible
    WHERE @column1 < CONVERT(DATETIME, '1/1/2016');
    GO
    
  • 非決定論的な関数呼び出しが含まれているため、次の関数は無効です。

    CREATE FUNCTION dbo.fn_example6 (@column1 DATETIME)
    RETURNS TABLE WITH SCHEMABINDING
    AS
    RETURN
    
    SELECT 1 AS is_eligible
    WHERE @column1 < DATEADD(day, - 60, GETDATE());
    GO
    
  • サブクエリが含まれているため、次の関数は無効です。

    CREATE FUNCTION dbo.fn_example7 (@column1 INT)
    RETURNS TABLE WITH SCHEMABINDING
    AS
    RETURN
    
    SELECT 1 AS is_eligible
    WHERE @column1 IN (
        SELECT SupplierID
        FROM Supplier
        WHERE STATUS = 'Defunct'
    );
    GO
    
  • 関数を定義するときに、代数演算子または組み込み関数を使用する式は定数に評価する必要があるため、次の関数は無効です。 代数式や関数呼び出しに列参照を含めることはできません。

    CREATE FUNCTION dbo.fn_example8 (@column1 INT)
    RETURNS TABLE WITH SCHEMABINDING
    AS
    RETURN
    
    SELECT 1 AS is_eligible
    WHERE @column1 % 2 = 0;
    GO
    
    CREATE FUNCTION dbo.fn_example9 (@column1 INT)
    RETURNS TABLE WITH SCHEMABINDING
    AS
    RETURN
    
    SELECT 1 AS is_eligible
    WHERE SQRT(@column1) = 30;
    GO
    
  • BETWEEN 演算子を同等の AND 式で置換した後、ここで説明するルールに違反するため、次の関数は無効です。

    CREATE FUNCTION dbo.fn_example10 (
        @column1 INT,
        @column2 INT
    )
    RETURNS TABLE
        WITH SCHEMABINDING
    AS
    RETURN
    
    SELECT 1 AS is_eligible
    WHERE (
        @column1 BETWEEN 1 AND 200
        OR @column1 = 300
    )
    AND @column2 > 1000;
    GO
    

    BETWEEN 演算子を同等の AND 式に置き換えた結果、置換前の関数は置換後の関数と等しくなっています。 プリミティブ条件で使用できるのは OR 論理演算子だけであるため、この関数は無効です。

    CREATE FUNCTION dbo.fn_example11 (
        @column1 INT,
        @column2 INT
    )
    RETURNS TABLE WITH SCHEMABINDING
    AS
    RETURN
    
    SELECT 1 AS is_eligible
    WHERE (
        @column1 >= 1
        AND @column1 <= 200
        OR @column1 = 300
    )
    AND @column2 > 1000;
    GO
    

Stretch Database がフィルター関数を適用するしくみ

Stretch Database では、CROSS APPLY 演算子を使用してテーブルにフィルター関数を適用し、対象となる行を決定します。 たとえば、次のように入力します。

SELECT * FROM stretch_table_name CROSS APPLY fn_stretchpredicate(column1, column2)

関数から行の空ではない結果が返された場合、その行は移行の対象になります。

既存のフィルター関数の置き換え

以前に指定したフィルター関数を置き換えるには、 ALTER TABLE ステートメントをもう一度実行し、 FILTER_PREDICATE パラメーターに新しい値を指定します。 たとえば、次のように入力します。

ALTER TABLE stretch_table_name SET (
	REMOTE_DATA_ARCHIVE = ON (
		FILTER_PREDICATE = dbo.fn_stretchpredicate2(column1, column2),
		MIGRATION_STATE = OUTBOUND
		/* replace OUTBOUND in this example, with the actual, desired migration state */
));

新しいインライン テーブル値関数には、次の要件があります。

  • 新しい関数では、前の関数よりも制約を減らす必要があります。

  • 古い関数に含まれていたすべての演算子を新しい関数に含める必要があります。

  • 古い関数に含まれていない演算子を新しい関数に含めることはできません。

  • 演算子の引数の順序は変更できません。

  • <, <=, >, >= 比較に含まれる定数値のみを、関数の制約が減るように変更できます。

有効な置換の例

次の関数が現在のフィルター関数であると仮定します。

CREATE FUNCTION dbo.fn_stretchpredicate_old (
    @column1 DATETIME,
    @column2 INT
)
RETURNS TABLE
    WITH SCHEMABINDING
AS
RETURN

SELECT 1 AS is_eligible
WHERE @column1 < CONVERT(DATETIME, '1/1/2016', 101)
    AND (
		@column2 < - 100
		OR @column2 > 100
	);
GO

(元の決算日よりも後の日付を指定した) 新しい日付定数によって関数の制約が減るため、次の関数は有効な置換です。

CREATE FUNCTION dbo.fn_stretchpredicate_new (
    @column1 DATETIME,
    @column2 INT
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN

SELECT 1 AS is_eligible
WHERE @column1 < CONVERT(DATETIME, '2/1/2016', 101)
    AND (
        @column2 < - 50
        OR @column2 > 50
	);
GO

無効な置換の例

(元の決算日よりも前の日付を指定した) 新しい日付定数によって関数の制約が減らないため、次の関数は無効な置換です。

CREATE FUNCTION dbo.fn_notvalidreplacement_1 (
    @column1 DATETIME,
    @column2 INT
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN

SELECT 1 AS is_eligible
WHERE @column1 < CONVERT(DATETIME, '1/1/2015', 101)
    AND (
		@column2 < - 100
		OR @column2 > 100
	);
GO

比較演算子の 1 つが削除されているため、次の関数は無効な置換です。

CREATE FUNCTION dbo.fn_notvalidreplacement_2 (
    @column1 DATETIME,
    @column2 INT
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN

SELECT 1 AS is_eligible
WHERE @column1 < CONVERT(DATETIME, '1/1/2016', 101)
    AND (@column2 < - 50);
GO

AND 論理演算子を使用して新しい条件が追加されているため、次の関数は無効な置換です。

CREATE FUNCTION dbo.fn_notvalidreplacement_3 (
    @column1 DATETIME,
    @column2 INT
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN

SELECT 1 AS is_eligible
WHERE @column1 < CONVERT(DATETIME, '1/1/2016', 101)
    AND (
        @column2 < - 100
        OR @column2 > 100
	)
    AND (@column2 <> 0);
GO

テーブルからのフィルター関数の削除

選択した行ではなく、テーブル全体を移行するには、FILTER_PREDICATE を null に設定して既存の関数を削除します。 たとえば、次のように入力します。

ALTER TABLE stretch_table_name

SET (
	REMOTE_DATA_ARCHIVE = ON (
		FILTER_PREDICATE = NULL,
		MIGRATION_STATE = OUTBOUND
		/* replace OUTBOUND in this example, with the actual, desired migration state */
));

フィルター関数を削除すると、テーブルのすべての行が移行の対象になります。 そのため、Azure からテーブルのすべてのリモート データを先に戻しておかない限り、後で同じテーブルにフィルター関数を指定することはできません。 この制限は、新しいフィルター関数を指定したときに移行の対象外になっている行が既に Azure に移行されているという状況を回避するために設けられています。

テーブルに適用されたフィルター関数の確認

テーブルに適用されたフィルター関数を確認するには、カタログ ビュー sys.remote_data_archive_tables を開き、 filter_predicate 列の値を確認します。 値が null の場合、テーブル全体がアーカイブの対象になります。 詳細については、「sys.remote_data_archive_tables (Transact-SQL)」をご覧ください。

フィルター関数のセキュリティに関する注意事項

db_owner 権限を持つ侵害されたアカウントは、次の操作を実行できます。

  • 大量のサーバー リソースを消費したり、長期間にわたって待機したりするテーブル値関数を作成し適用して、サービス拒否攻撃を仕掛ける。

  • 読み取りアクセスを明示的に拒否されているユーザーが、テーブルの内容を推測できるようにするテーブル値関数を作成して適用する。

関連項目