使用筛选器函数选择要迁移的行 (Stretch Database)Select rows to migrate by using a filter function (Stretch Database)

适用于:是SQL Server(仅从 2016 开始的 Windows)否Azure SQL 数据库否Azure SQL 数据仓库否并行数据仓库APPLIES TO: yesSQL Server (Windows only starting with 2016) noAzure SQL Database noAzure SQL Data Warehouse 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 语句以对表启用 Stretch 并指定筛选器函数。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

<谓词> 可以包含一个条件,或使用 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

如果将 BETWEEN 和 NOT BETWEEN 运算符替换为等效的 AND 和 OR 表达式后,生成的函数符合此处所述的规则,则可以使用 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. 如果在传递列名称时指定三个部分组成的名称,则对已启用 Stretch 的表的后续查询将失败。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 检查 Stretch Database 监视器 中的状态,或者可以查询 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. 此用法有效,因为将 BETWEEN 和 NOT BETWEEN 运算符替换为等效的 AND 和 OR 表达式后,生成的函数符合此处所述的规则。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  
    
  • 下面的函数无效,因为将 BETWEEN 运算符替换为等效的 AND 表达式后,它违反了此处所述的规则。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  
    

    将 BETWEEN 运算符替换为等效的 AND 表达式后,前面的函数等效于下面的函数。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)