USE FEDERATION (Azure SQL Database)

Connects to a federation.

Transact-SQL Syntax Conventions (Transact-SQL)

Syntax

USE FEDERATION ROOT
    WITH RESET

USE FEDERATION federation_name (distribution_name = value)
    WITH [FILTERING={ON|OFF},] RESET
[;]

Arguments

• ROOT
  Points an existing connection to the federation root. If connected to a federation member, the connection is reset back to the federation root database. When connected to any other database, it has no effect.

• federation_name
  The name of the federation targeted by the connection.

• distribution_name
  The name of the federation key for this federation.

• value
  The value to use for connecting to the federation member. The federation member servicing the federation value becomes the federation member that the connection is established to.

  The value must comply with the type that the federation specified in the federation_name defined.

  The value cannot be NULL.

• WITH FILTERING=ON|OFF
  When this argument is not presented, the filtering is set to ON.

  When FILTERING is set to OFF, the connection scope is set to the full range covered by the federation member containing the specified key value. The connection behaves the same if it were connected to the member through its physical federation member name (the database name).

  Filtering is set to OFF when applications want to work with all data in the federation member (for example for scanning the entire range) and have built logic already to deal with filtering to a single atomic federation atomic unit instance in their app.

  When FILTERING is set to ON, the connection scope is set to the federation key instance in a federation member rather than to the full range of federation member. In this mode, the query processor adds a predicate (fed_column=value) on the federation column to every query that refers to a federated table.

  Filtering is set to ON when applications want to scope their access to an atomic federation atomic unit automatically (for example just to tenant_id 55).

• WITH RESET
  This is a required keyword that makes the connection reset explicit.

Remarks

The USE FEDERATION statement provides efficient connection pooling behavior for existing connectivity libraries by allowing applications to first connect to the database that contains the federation object and through the USE FEDERATION statement, switch to the specific federation member. This ensures that the client connection pool is smaller than it would be if it had to address individual federation members or federation atomic unit values.

USE FEDERATION can be executed on connection at any point in the lifetime of the connection but must be the only statement in the batch and does not support parameterization.

USE FEDERATION rewires a connection from the current federation member or the root database to the federation root, or to the specified value that is contained within a federation member. When a connection is rewired with USE FEDERATION, the connection is reestablished. Thus all existing settings and the context of the connection is lost. This is equivalent to running sp_reset_connection when a connection is retrieved from a connection pool.

Since the master database cannot contain a federation, USE FEDERATION ROOT ran within the master database has no effect.

USE FEDERATION with a federation name that does not exist returns an error such as ‘Federation <federation name> does not exist’.

Using FILTERING=ON allows limiting of the view of the connection to only a given federation key instance. Connecting to a federation key instance binds the query to a specific federation atomic unit. This modifies the semantics of queries issued on the connection by introducing a predicate P. P is injected for all federated tables in a given query as Federation_Column =@filtering_value.

Any query that attempts to load, insert, or move a row out of the current federation filtering value is illegal and will fail, as though there were a check constraint on the target table enforcing P."

Example

The following example demonstrates how to route the connection to federation root.

using (SqlConnection connection = new SqlConnection(connectionString)) 
{ 
    connection.Open(); 
 
    using (SqlCommand command = connection.CreateCommand()) 
    { 
        // Route the connection to federation root 
        // Note that the CommandText should not contain any additional commends
        command.CommandText = "USE FEDERATION ROOT WITH RESET"; 
        command.ExecuteNonQuery(); 
    } 
}