使用篩選函數選取要移轉的資料列 (Stretch Database)Select rows to migrate by using a filter function (Stretch Database)

適用於: 是SQL Server 2016 與更新版本 (僅限 Windows) 否Azure SQL Database 否Azure Synapse Analytics (SQL DW) 否平行處理資料倉儲APPLIES TO: yesSQL Server 2016 and later (Windows only) noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

若您將原始資料儲存在其他資料表中,您可以設定 Stretch Database 以移轉整個資料表。If you store cold data in a separate table, you can configure Stretch Database to migrate the entire table. 若您的資料表同時包含作用及原始資料,則可以指定篩選器述詞,以選取要移轉的資料列。If your table contains both hot and cold data, on the other hand, you can specify a filter predicate to select the rows to migrate. 篩選器述詞是內嵌資料表值函數。The filter predicate is an inline table-valued function. 本文描述如何撰寫內嵌資料表值函式,以選取要移轉的資料列。This article describes how to write an inline table-valued function to select rows to migrate.

重要

若您提供執行狀況不佳的篩選函數,資料移轉也無法順利執行。If you provide a filter function that performs poorly, data migration also performs poorly. Stretch Database 使用 CROSS APPLY 運算子,將篩選函數套用至資料表。Stretch Database applies the filter function to the table by using the CROSS APPLY operator.

若您未指定篩選函數,則會移轉整個資料表。If you don't specify a filter function, the entire table is migrated.

當您執行 [啟用資料庫的延展功能精靈] 時,可以移轉整個資料表,也可以在精靈中指定簡單的篩選函數。When you run the Enable Database for Stretch Wizard, you can migrate an entire table or you can specify a simple filter function in the wizard. 如果您想要使用不同類型的篩選函數來選取要移轉的資料列,請執行下列其中一項操作。If you want to use a different type of filter function to select rows to migrate, do one of the following things.

  • 結束精靈,然後執行 ALTER TABLE 陳述式來啟用資料表的延展功能以及指定篩選函數。Exit the wizard and run the ALTER TABLE statement to enable Stretch for the table and to specify a filter function.

  • 結束精靈之後,請執行 ALTER TABLE 陳述式來指定篩選函數。Run the ALTER TABLE statement to specify a filter function after you exit the wizard.

本文稍後會描述用於新增函式的 ALTER TABLE 語法。The ALTER TABLE syntax for adding a function is described later in this article.

篩選函數的基本需求Basic requirements for the filter function

Stretch Database 篩選器述詞需要的內嵌資料表值函數如同下列範例所示。The inline table-valued function required for a Stretch Database filter predicate looks like the following example.

CREATE FUNCTION dbo.fn_stretchpredicate(@column1 datatype1, @column2 datatype2 [, ...n])  
RETURNS TABLE  
WITH SCHEMABINDING   
AS   
RETURN  SELECT 1 AS is_eligible  
        WHERE <predicate>  

函式的參數必須是來自資料表的資料欄識別碼。The parameters for the function have to be identifiers for columns from the table.

需要結構描述繫結,才能避免篩選函數所使用的資料行遭到卸除或改變。Schema binding is required to prevent columns that are used by the filter function from being dropped or altered.

傳回值Return value

若函數傳回非空白結果,資料列便符合遷移資格。If the function returns a non-empty result, the row is eligible to be migrated. 否則若函數未傳回結果,則資料列不適合進行遷移。Otherwise - that is, if the function doesn't return a result - the row is not eligible to be migrated.

條件Conditions

<predicate> 可包含一個條件,或使用 AND 邏輯運算子聯結多個條件。The <predicate> can consist of one condition, or of multiple conditions joined with the AND logical operator.

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

每個條件皆可以包含一個基本條件,或是包含以 OR 邏輯運算子結合的數個基本條件。Each condition in turn can consist of one primitive condition, or of multiple primitive conditions joined with the OR logical operator.

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

基本條件Primitive conditions

基本條件可執行下列其中一個比較。A primitive condition can do one of the following comparisons.

<primitive_condition> ::=   
{  
<function_parameter> <comparison_operator> constant  
| <function_parameter> { IS NULL | IS NOT NULL }  
| <function_parameter> IN ( constant [ ,...n ] )  
}  
  
  • 比較函數參數與常數運算式。Compare a function parameter to a constant expression. 例如: @column1 < 1000For example, @column1 < 1000.

    以下範例會檢查 date 資料行的值是否為 < 1/1/2016。Here's an example that checks whether the value of a date column is < 1/1/2016.

    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 運算子套用至函式參數。Apply the IS NULL or IS NOT NULL operator to a function parameter.

  • 使用 IN 運算子來比較函式參數及常數值清單。Use the IN operator to compare a function parameter to a list of constant values.

    以下範例會檢查 shipment_status 資料行的值是否為 IN (N'Completed', N'Returned', N'Cancelled')Here's an example that checks whether the value of a shipment_status column is 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 operators

下列為支援的比較運算子。The following comparison operators are supported.

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

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

常數運算式Constant expressions

您在篩選函數中使用的常數可以是任何確定性的運算式,其可在定義函數時接受評估。The constants that you use in a filter function can be any deterministic expression that can be evaluated when you define the function. 常數運算式可以包含下列項目。Constant expressions can contain the following things.

  • 常值。Literals. 例如: N'abc', 123For example, N'abc', 123.

  • 代數運算式。Algebraic expressions. 例如: 123 + 456For example, 123 + 456.

  • 確定性函數。Deterministic functions. 例如: SQRT(900)For example, SQRT(900).

  • 使用 CAST 或 CONVERT 的確定性轉換。Deterministic conversions that use CAST or CONVERT. 例如: CONVERT(datetime, '1/1/2016', 101)For example, CONVERT(datetime, '1/1/2016', 101).

其他運算式Other expressions

在使用對等 AND 及 OR 運算式取代 BETWEEN 及 NOT BETWEEN 運算子後,若產生的函數符合此處所述的規則,您就可以使用 BETWEEN 及 NOT BETWEEN 運算子。You can use the BETWEEN and NOT BETWEEN operators if the resulting function conforms to the rules described here after you replace the BETWEEN and NOT BETWEEN operators with the equivalent AND and OR expressions.

您無法使用子查詢或非確定性的函數,例如 RAND() 或 GETDATE()。You can't use subqueries or non-deterministic functions such as RAND() or GETDATE().

將篩選函數加入資料表Add a filter function to a table

執行 ALTER TABLE 陳述式,以將篩選函數加入資料表中,並將現有內嵌資料表值函數指定為 FILTER_PREDICATE 參數的值。Add a filter function to a table by running the ALTER TABLE statement and specifying an existing inline table-valued function as the value of the FILTER_PREDICATE parameter. 例如:For example:

ALTER TABLE stretch_table_name SET ( REMOTE_DATA_ARCHIVE = ON (  
    FILTER_PREDICATE = dbo.fn_stretchpredicate(column1, column2),  
    MIGRATION_STATE = <desired_migration_state>  
) )  
  

當您將函數繫結至資料表作為述詞後,下列描述即成立。After you bind the function to the table as a predicate, the following things are true.

  • 下一次資料移轉時,只會遷移函數傳回非空白值的資料列。The next time data migration occurs, only the rows for which the function returns a non-empty value are migrated.

  • 函數所使用的資料行是結構描述繫結。The columns used by the function are schema bound. 只要資料表仍使用函數作為其篩選器述詞,您就無法變更這些資料行。You can't alter these columns as long as a table is using the function as its filter predicate.

只要資料表仍使用函數作為其篩選器述詞,您就無法卸除內嵌資料表值函數。You can't drop the inline table-valued function as long as a table is using the function as its filter predicate.

提示

若要改善篩選函數的效能,請在函數使用的資料行上建立索引。To improve the performance of the filter function, create an index on the columns used by the function.

將資料行名稱傳遞給篩選函數Passing column names to the filter function

當您指派篩選函數給資料表時,請使用一段式名稱來指定傳遞給篩選函數的資料行名稱。When you assign a filter function to a table, specify the column names passed to the filter function with a one-part name. 如果您在傳遞資料行名稱時指定的是三段式名稱,後續針對已啟用延展功能之資料表的查詢將會失敗。If you specify a three-part name when you pass the column names, subsequent queries against the Stretch-enabled table will fail.

例如,如果您指定三段式資料行名稱 (如以下範例所示),陳述式將會執行成功,但後續針對資料表的查詢將會失敗。For example, if you specify a three-part column name as shown in the following example, the statement will run successfully, but subsequent queries against the table will fail.

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

請改為使用一段式資料行名稱來指定篩選函數,如以下範例所示。Instead, specify the filter function with a one-part column name as shown in the following example.

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

在執行精靈後新增篩選函數Add a filter function after running the Wizard

如果您想要使用無法在 [啟用資料庫的延展功能精靈] 中建立的函數,您可以在結束精靈後,執行 ALTER TABLE 陳述式來指定函數。If you want use a function that you can't create in the Enable Database for Stretch Wizard, you can run the ALTER TABLE statement to specify a function after you exit the wizard. 不過,您必須先停止已經在進行中的資料移轉並回復已移轉的資料,才能套用函數。Before you can apply a function, however, you have to stop the data migration that's already in progress and bring back migrated data. (如需為什麼必須這麼做的詳細資訊,請參閱 取代現有的篩選函數。)(For more info about why this is necessary, see Replace an existing filter function.)

  1. 反轉移轉方向並回復已經移轉的資料。Reverse the direction of migration and bring back the data already migrated. 這項作業開始之後,即無法將其取消。You can't cancel this operation after it starts. 您也會因輸出資料傳輸 (輸出) 而在 Azure 上產生費用。You also incur costs on Azure for outbound data transfers (egress). 如需詳細資訊,請參閱 Azure 定價機制For more info, see How Azure pricing works.

    ALTER TABLE <table name>  
        SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = INBOUND ) ) ;   
    
  2. 等待移轉完成。Wait for migration to finish. 您可以在 SQL Server Management Studio 的 [延展資料庫監視器] 中或查詢 sys.dm_db_rda_migration_status 檢視,來查看狀態。You can check the status in Stretch Database Monitor from SQL Server Management Studio, or you can query the sys.dm_db_rda_migration_status view. 如需詳細資訊,請參閱 資料移轉的監視及疑難排解sys.dm_db_rda_migration_statusFor more info, see Monitor and troubleshoot data migration or sys.dm_db_rda_migration_status.

  3. 建立您想要套用至資料表的篩選函數。Create the filter function that you want to apply to the table.

  4. 將函數加入資料表,然後重新啟動資料移轉來移轉到 Azure。Add the function to the table and restart data migration to Azure.

    ALTER TABLE <table name>  
        SET ( REMOTE_DATA_ARCHIVE  
            (           
                FILTER_PREDICATE = <predicate>,  
                MIGRATION_STATE = OUTBOUND  
            )  
            );   
    

依日期篩選資料列Filter rows by date

下列範例會遷移資料列,其中 date 資料行包含早於 2016 年 1 月 1 日的值。The following example migrates rows where the date column contains a value earlier than January 1, 2016.

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

依狀態資料行中的值篩選資料列Filter rows by the value in a status column

下列範例會遷移資料列,其中 status 資料行包含其中一個指定的值。The following example migrates rows where the status column contains one of the specified values.

-- 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 rows by using a sliding window

若要使用滑動視窗篩選資料列,請牢記下列篩選函數的需求。To filter rows by using a sliding window, keep in mind the following requirements for the filter function.

  • 函數必須為確定性函數。The function has to be deterministic. 因此您無法建立會隨著時間推移,而自動重新計算的滑動視窗函數。Therefore you can't create a function that automatically recalculates the sliding window as time passes.

  • 此函式會使用結構描述繫結。The function uses schema binding. 因此您無法僅透過呼叫 ALTER FUNCTION 來移動滑動視窗,以每天「就地」更新函數。Therefore you can't simply update the function "in place" every day by calling ALTER FUNCTION to move the sliding window.

遵循下列範例開始使用篩選函數,此範例會移遷資料列,其中 systemEndTime 資料行包含早於 2016 年 1 月 1 日的值。Start with a filter function like the following example, which migrates rows where the systemEndTime column contains a value earlier than January 1, 2016.

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

將篩選函數套用至資料表。Apply the filter function to the table.

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

當您想要更新滑動視窗時,請執行下列動作。When you want to update the sliding window, do the following things.

  1. 建立新的函數,以指定新的滑動視窗。Create a new function that specifies the new sliding window. 下列範例選取的日期早於 2016 年 1 月 2 日,而非 2016 年 1 月 1 日。The following example selects dates earlier than January 2, 2016, instead of January 1, 2016.

  2. 藉由呼叫 ALTER TABLE,以新的篩選函數取代先前的篩選函數,如下列範例所示。Replace the previous filter function with the new one by calling ALTER TABLE, as shown in the following example.

  3. 您可以選擇呼叫 DROP FUNCTION,卸除您不再使用的篩選函數。Optionally, drop the previous filter function that you're no longer using by calling DROP FUNCTION. (範例中未顯示此步驟)。(This step is not shown in the example.)

BEGIN TRAN  
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(SysEndTime),  
                       MIGRATION_STATE = OUTBOUND  
               )  
        )   
COMMIT ;  
  

有效篩選函數的其他範例More examples of valid filter functions

  • 下列範例使用 AND 邏輯運算子將兩個基本條件結合。The following example combines two primitive conditions by using the AND logical operator.

    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  
    ) )  
    
    
  • 下列範例以 CONVERT 使用數個條件及一個確定性轉換。The following example uses several conditions and a deterministic conversion with 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  
    
    
  • 下列範例使用數學運算子及函數。The following example uses mathematical operators and functions.

    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 運算子。The following example uses the BETWEEN and NOT BETWEEN operators. 在使用對等 AND 及 OR 運算式取代 BETWEEN 及 NOT BETWEEN 運算子後,產生的函數符合此處所述的規則,所以使用方式有效。This usage is valid because the resulting function conforms to the rules described here after you replace the BETWEEN and NOT BETWEEN operators with the equivalent AND and OR expressions.

    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 運算式之後,前面的函式與後面的函式相等。The preceding function is equivalent to the following function after you replace the BETWEEN and NOT BETWEEN operators with the equivalent AND and OR expressions.

    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  
    
    

無效篩選函數的範例Examples of filter functions that aren't valid

  • 因為下列函數包含非確定性轉換,所以無效。The following function isn't valid because it contains a non-deterministic conversion.

    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  
    
    
  • 因為下列函數包含非確定性函數呼叫,所以無效。The following function isn't valid because it contains a non-deterministic function call.

    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  
    
    
  • 因為下列函數包含子查詢,所以無效。The following function isn't valid because it contains a subquery.

    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  
    
    
  • 因為必須在定義函數時,將使用代數運算子或內建函數的運算式評估為常數,所以下列函數無效。The following functions aren't valid because expressions that use algebraic operators or built-in functions must evaluate to a constant when you define the function. 您無法包含以代數運算式或函式呼叫呈現的資料欄參考。You can't include column references in algebraic expressions or function calls.

    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  
    
    
  • 在使用對等 AND 運算式取代 BETWEEN 運算子後,因為下列函數違反此處所述的規則,所以無效。The following function isn't valid because it violates the rules described here after you replace the BETWEEN operator with the equivalent AND expression.

    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運算子後,之前的函數即相當於下列函數。The preceding function is equivalent to the following function after you replace the BETWEEN operator with the equivalent AND expression. 因為基本條件只能使用 OR 邏輯運算子,所以此函數無效。This function isn't valid because primitive conditions can only use the OR logical operator.

    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 如何套用篩選函數How Stretch Database applies the filter function

Stretch Database 會使用 CROSS APPLY 運算子將篩選函數套用至資料表,並判斷合格的資料列。Stretch Database applies the filter function to the table and determines eligible rows by using the CROSS APPLY operator. 例如:For example:

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

若函數為資料列傳回非空白結果,資料列便符合遷移資格。If the function returns a non-empty result for the row, the row is eligible to be migrated.

取代現有的篩選函數Replace an existing filter function

您可再次執行 ALTER TABLE 陳述式,並為 FILTER_PREDICATE 參數指定新的值,以取代先前指定的篩選函數。You can replace a previously specified filter function by running the ALTER TABLE statement again and specifying a new value for the FILTER_PREDICATE parameter. 例如:For example:

ALTER TABLE stretch_table_name SET ( REMOTE_DATA_ARCHIVE = ON (  
    FILTER_PREDICATE = dbo.fn_stretchpredicate2(column1, column2),  
    MIGRATION_STATE = <desired_migration_state>  
  

新的內嵌資料表值函數具有下列需求。The new inline table-valued function has the following requirements.

  • 新的函數限制不能多於先前的函數。The new function has to be less restrictive than the previous function.

  • 舊函數中的所有運算子都必須存在於新的函數中。All the operators that existed in the old function must exist in the new function.

  • 新函式不能包含不存在於舊函式中的運算子。The new function can't contain operators that don't exist in the old function.

  • 不能變更運算子引數的順序。The order of operator arguments can't change.

  • 只有屬於 <, <=, >, >= 比較的常數值,可透過減少函數限制的方式進行變更。Only constant values that are part of a <, <=, >, >= comparison can be changed in a way that makes the function less restrictive.

有效取代的範例Example of a valid replacement

假設下列函數是目前的篩選函數。Assume that the following function is the current filter function.

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  
  

因為新的日期常數 (指定了較晚的截止日期),使函數的限制變少,所以下列函數是有效取代。The following function is a valid replacement because the new date constant (which specifies a later cutoff date) makes the function less restrictive.

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  
  

無效取代的範例Examples of replacements that aren't valid

因為新的日期常數 (指定了較早的截止日期) 並未使函數限制變少,所以下列函數不是有效取代。The following function isn't a valid replacement because the new date constant (which specifies an earlier cutoff date) doesn't make the function less restrictive.

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  
  

因為已經移除其中一個比較運算子,所以下列函數不是有效取代。The following function isn't a valid replacement because one of the comparison operators has been removed.

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 邏輯運算子加入新的條件,所以下列函數不是有效取代。The following function isn't a valid replacement because a new condition has been added with the AND logical operator.

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  
  

從資料表移除篩選函數Remove a filter function from a table

若要移轉整個資料表,而非只移轉選取的資料列,請將 FILTER_PREDICATE 設定為 null 來移除現有的函數。To migrate the entire table instead of selected rows, remove the existing function by setting FILTER_PREDICATE to null. 例如:For example:

ALTER TABLE stretch_table_name SET ( REMOTE_DATA_ARCHIVE = ON (  
    FILTER_PREDICATE = NULL,  
    MIGRATION_STATE = <desired_migration_state>  
) )  
  

移除篩選函數後,則資料表中的所有資料列都適合進行移轉。After you remove the filter function, all rows in the table are eligible for migration. 如此一來,您稍後便無法為相同的資料表指定篩選函數,除非您先回復所有 Azure 資料表的遠端資料。As a result, you cannot specify a filter function for the same table later unless you bring back all the remote data for the table from Azure first. 此限制用以避免以下情況:當您提供已移轉至 Azure 的新篩選函數時,資料列不符合移轉資格。This restriction exists to avoid the situation where rows that are not eligible for migration when you provide a new filter function have already been migrated to Azure.

檢查篩選函數是否已套用至資料表Check the filter function applied to a table

若要檢查篩選函數是否已套用至資料表,請開啟目錄檢視 sys.remote_data_archive_tables ,並檢查 filter_predicate 資料行的值。To check the filter function applied to a table, open the catalog view sys.remote_data_archive_tables and check the value of the filter_predicate column. 若值為 null,則整個資料表都適合進行封存。If the value is null, the entire table is eligible for archiving. 如需詳細資訊,請參閱 sys.remote_data_archive_tables (Transact-SQL)For more info, see sys.remote_data_archive_tables (Transact-SQL).

篩選函數的安全性注意事項Security notes for filter functions

遭盜用的帳戶若具備 db_owner 權限,便能夠執行下列操作。A compromised account with db_owner privileges can do the following things.

  • 建立並套用耗用大量伺服器資源或長期等待而導致阻斷服務的資料表值函數。Create and apply a table-valued function that consumes large amounts of server resources or waits for an extended period resulting in a denial of service.

  • 建立並套用可能推斷出已明確拒絕使用者讀取之資料表內容的資料表值函數。Create and apply a table-valued function that makes it possible to infer the content of a table for which the user has been explicitly denied read access.

另請參閱See Also

ALTER TABLE (Transact-SQL)ALTER TABLE (Transact-SQL)