sp_check_join_filter (Transact-SQL)

Is used to verify a join filter between two tables to determine if the join filter clause is valid. This stored procedure also returns information about the supplied join filter, including if it can be used with precomputed partitions for the given table. This stored procedure is executed at the Publisher on the publication. For more information, see Optimizing Parameterized Filter Performance with Precomputed Partitions.

Transact-SQL Syntax Conventions

Syntax

sp_check_join_filter [ @filtered_table = ] 'filtered_table'
        , [@join_table = ] 'join_table'
        , [ @join_filterclause = ] 'join_filterclause'

Arguments

• [ @filtered_table= ] 'filtered_table'
  Is the name of a filtered table. filtered_table is nvarchar(400), with no default.

• [ @join_table= ] 'join_table'
  Is the name of a table being joined to filtered_table. join_table is nvarchar(400), with no default.

• [ @join_filterclause = ] 'join_filterclause'
  Is the join filter clause being tested. join_filterclause is nvarchar(1000), with no default.

Return Code Values

0 (success) or 1 (failure)

Result Sets

Column name	Data type	Description
can_use_partition_groups	bit	Is if the publication qualifies for precomputed partitions; where 1 means that precomupted partitions can be used, and 0 means that they cannot be used.
has_dynamic_filters	bit	Is if the supplied filter clause includes at least one parameterized filtering function; where 1 means that a parameterized filtering function is used, and 0 means that such a function is not used.
dynamic_filters_function_list	nvarchar(500)	List of the functions in the filter clause that define a parameterized filter for an article, where each function is separated by a semi-colon.
uses_host_name	bit	If the HOST_NAME() function is used in the filter clause, where 1 means that this function is present.
uses_suser_sname	bit	If the SUSER_SNAME() function is used in the filter clause, where 1 means that this function is present.

Remarks

sp_check_join_filter is used in merge replication.

sp_check_join_filter can be executed against any related tables even if they are not published. This stored procedure can be used to verify a join filter clause before defining a join filter between two articles.

Permissions

Only members of the sysadmin fixed server role or db_owner fixed database role can execute sp_check_join_filter.

See Also

Reference

Replication Stored Procedures (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance