SALES: 1-800-867-1380

Federation Guidelines and Limitations

Updated: January 20, 2015

This topic describes guidelines and limitations for federations in Microsoft Azure SQL Database. The general guidelines and limitations are covered in the following sections.

Federations will be retired with Web and Business service tiers in Azure SQL Database. (See Web and Business Edition Sunset FAQ.) Consider using Elastic Scale for Azure SQL Database to build a sharded, scale-out solution for your data tier. To try it, see Get Started with Azure SQL Database Elastic Scale Preview.

Federations can contain both federated tables and reference tables. Federated tables are tables created using the FEDERATED ON clause, and contain a column that is associated with the distribution key for the containing federation. Reference tables are tables within a federation that were not created using the FEDERATED ON clause, and have no special association with the federation distribution key. For more information on creating tables, see CREATE TABLE.

Federated tables have the following limitations:

  • The federation column of the federated table can only contain data that conforms to the federation member range_low inclusive and range_high exclusive.

  • The data type of the federation column must exactly match the data type that is defined in the federation definition.

  • All unique and clustered indexes on the federated table must contain the federation column. The order in which the federation column appears in the index can be different from the key ordinal in the federation.

  • Federation column values cannot be updated to values outside the federation member range.

  • The federation column cannot be a persisted or non-persisted computed column.

  • Indexed Views are not supported in federation members.

  • Federation columns cannot be NULLable.

  • All foreign key constraints on federated tables need to include the federation column on both the referrer and the referenced tables at the same ordinal in the foreign key. Reference tables cannot have foreign key relationships with federated tables. Federated tables can have foreign key relationships with reference tables without restrictions.

  • You can drop tables created with the FEDERATED ON clause normally. You can also use ALTER TABLE to change all properties of a federated table except federation attributes such as the federation key. To change a reference table into a federated table or a federated table into a reference table, you must create new tables with the desired properties and drop the existing table.

  • When a table is marked with STATISTICS_NORECOMPUTE, federation operations like SPLIT do not invalidate or recalculate statistics. This could cause execution plan issues after repartitioning operations such as SPLIT.

  • Federated members do not support the identity property

  • Federated members do not support the timestamp and rowversion data type.

All general table metadata for federated tables is available through standard system views. Federation specific properties are available through sys.federated_table_columns.

Reference tables are not automatically distributed across all federation members. While reference tables can be replicated across members of a federation manually, there is no automatic replication process.

Reference tables often contain supporting information for queries against federated tables so that you do not have to perform queries against multiple databases. For example, while customer information may be federated and spread across multiple member databases, there is nothing to be gained from federating state and zip code reference information. However you may want to store a copy of the state and zip code information in each federation member so that your queries do not have to span multiple databases.

Geography and geometry types cannot be used as the data type of the column that a table is federated on; however they can be part of the federated table. There are no other limitations on using spatial data with federations.

After a SPLIT or DROP operation, spatial indexes stay consistent and intact in the destination federation members.

The hierarchyid type cannot be used as the data type of the column that a table is federated on; however it can be part of the federated table. There are no other limitations on using hierarchyid with federations.

After a SPLIT or DROP operation, hierarchyid indexes stay consistent and intact in the destination federation members.

Connections to a federation are performed through the USE FEDERATION statement. This statement automatically routes the connection to the correct member within a federation, eliminating the need to know the physical database name when accessing the data. Specifying the federation distribution key and a value will establish a connection to the corresponding member database within the federation.

Authentication and authorization to a database containing a federation is accomplished as normal. Logins and users determine connectivity; roles manage the grouping of principals. However, principals within a database that contains a federation are scoped only to the federation root database and are not automatically applied to federation members. For more information on users and roles, see Managing Database Federations (Azure SQL Database)

A database copy cannot be performed on a database that contains federations. Creating a federation fails if a database copy operation is active in the database. Database copy cannot be performed on federation members either.

Federation operations are not applied to the federation root quota. If the root database exceeds quota, you will still be able to perform SPLIT and DROP operations. Likewise, if the size quota is exceeded for a federation member, SPLIT and DROP operations can still be performed as long as the destination database quota will not be exceeded by the operation.

At the completion of repartitioning operations such as SPLIT and DROP, connections are dropped. This means that connection properties such as SET options, transaction isolation level settings, or variables are also reset. The SPLIT operation creates a new physical database, which means that the following Transact-SQL properties cannot be maintained across SPLIT operations.


Transact-SQL SQL Database Support Limitations Support in databases with Federations

Timestamp and rowversion data type

SQL Database may not preserve the uncommitted timestamp and rowversion values of the current database (DBTS) across failovers.

Timestamp and rowversion data type is not supported in federation members.

Timestamp and rowversion related functions such as @@dbts returns values when no timestamp and rowversion exists in a given database.

SYSUTCDATETIME(),SYSDATETIMEOFFSET(),SYSDATETIME(),getdate(), getutcdate()current_timestamp

SQL Database may report timestamp and rowversion from the local computer and may report times that are further in future or in the past across failovers.

Supported with the same constraints across repartitioning operations.


Principal SIDs are guaranteed to be the same across federation members and federation root for a given principal name. However principal ids may change after a repartitioning operations such as DROP.

IDENTITY property on columns

IDENTITY property is not supported in federation members. Identity related functions such as IDENT_CURRENT, IDENT_SEED, IDENT_INCR, SCOPE_IDENTITY always return null since no identity columns can exist in federation members.

OBJECT_ID and related functions that work with user defined objects; object_id(…), object_name(object_id), type_id(…), type_name(type_id)

Object_id of any object may change for user defined objects after a repartitioning operation such as DROP.

All Azure SQL Database database settings are supported; however changes to the value of an option on the federation root database will not change the option of any federation members.

See Also

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
© 2015 Microsoft