共用方式為

使用篩選函數選取要移轉的資料列 (Stretch Database)

  • 發行項

適用於：SQL Server 2016 (13.x) 和更新版本 - 僅限 Windows

重要

Stretch Database 在 SQL Server 2022 (16.x) 及 Azure SQL 資料庫中已被取代。 資料庫引擎的未來版本將移除此功能。 請避免在新的開發工作中使用這項功能，並規劃修改目前使用這項功能的應用程式。

若您將原始資料儲存在其他資料表中，您可以設定 Stretch Database 以移轉整個資料表。 若您的資料表同時包含作用及原始資料，則可以指定篩選器述詞，以選取要移轉的資料列。 篩選器述詞是內嵌資料表值函數。 本文描述如何撰寫內嵌資料表值函式，以選取要移轉的資料列。

重要

若您提供執行狀況不佳的篩選函數，資料移轉也無法順利執行。 Stretch Database 使用 CROSS APPLY 運算子，將篩選函數套用至資料表。

若您未指定篩選函數，則會移轉整個資料表。

當您執行啟用資料庫的延展功能精靈時，可以移轉整份資料表，也可以在精靈中指定篩選函數。 如果您想要使用不同類型的篩選函數來選取要移轉的資料列，請執行下列其中一項操作。

  • 結束精靈，然後執行 ALTER TABLE 陳述式來啟用資料表的延展功能以及指定篩選函數。

  • 結束精靈之後，請執行 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> 可包含一個條件，或使用 AND 邏輯運算子聯結多個條件。

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

每個條件皆可以包含一個基本條件，或是包含以 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) 。

其他運算式

在使用對等 AND 及 OR 運算式取代 BETWEEN 及 NOT BETWEEN 運算子後，若產生的函數符合此處所述的規則，您就可以使用 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 */
));

當您將函數繫結至資料表作為述詞後，下列描述即成立。

  • 下一次資料移轉時，只會遷移函數傳回非空白值的資料列。

  • 函數所使用的資料行是結構描述繫結。 只要資料表仍使用函數作為其篩選器述詞，您就無法變更這些資料行。

只要資料表仍使用函數作為其篩選器述詞，您就無法卸除內嵌資料表值函數。

若要改善篩選函數的效能，請在函數使用的資料行上建立索引。

將資料行名稱傳遞給篩選函數

當您指派篩選函數給資料表時，請使用一段式名稱來指定傳遞給篩選函數的資料行名稱。 如果您在傳遞資料行名稱時指定的是三段式名稱，後續針對已啟用延展功能之資料表的查詢將會失敗。

例如，如果您指定三段式資料行名稱 (如以下範例所示)，陳述式將會執行成功，但後續針對資料表的查詢將會失敗。

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

請改為使用一段式資料行名稱來指定篩選函數，如以下範例所示。

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

在執行精靈後新增篩選函數

如果您想要使用無法在啟用資料庫的延展功能精靈中建立的函數，您可以在結束精靈後，執行 ALTER TABLE 陳述式來指定函數。 不過，您必須先停止已經在進行中的資料移轉並回復已移轉的資料，才能套用函數。 (如需為什麼必須這麼做的詳細資訊，請參閱 取代現有的篩選函數。)

  1. 反轉移轉方向並回復已經移轉的資料。 這項作業開始之後，即無法將其取消。 您也會因輸出資料傳輸 (輸出) 而在 Azure 上產生費用。 如需詳細資訊，請參閱 Azure 定價機制。

    ALTER TABLE [<table name>] SET (
    REMOTE_DATA_ARCHIVE (
        MIGRATION_STATE = INBOUND
));

  2. 等待移轉完成。 您可以在 SQL Server Management Studio 的 [延展資料庫監視器] 中或查詢 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 資料行包含其中一個指定的值。

-- 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

使用滑動視窗篩選資料列

若要使用滑動視窗來篩選資料列，請記住 filter 函式的下列需求。

  • 函數必須為確定性函數。 因此您無法建立會隨著時間推移，而自動重新計算的滑動視窗函數。

  • 此函式會使用結構描述繫結。 因此，您無法每天僅為「就地」更新函數，而呼叫 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 月 2 日，而非 2016 年 1 月 1 日。

  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 邏輯運算子將兩個基本條件結合。

    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 運算子。 在使用對等 AND 及 OR 運算式取代 BETWEEN 及 NOT BETWEEN 運算子後，產生的函數符合此處所述的規則，所以使用方式有效。

    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

    在使用對等 AND 運算式取代 BETWEEN運算子後，之前的函數即相當於下列函數。 因為基本條件只能使用 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

因為已經移除其中一個比較運算子，所以下列函數不是有效取代。

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 權限，便能夠執行下列操作。

  • 建立並套用耗用大量伺服器資源或長期等待而導致阻斷服務的資料表值函數。

  • 建立並套用可能推斷出已明確拒絕使用者讀取之資料表內容的資料表值函數。

另請參閱