Federation Guidelines and Limitations

The syntax for creating a federated table is described in CREATE TABLE (Windows Azure SQL Database). The following is an example of creating a federation named CustomerFederation, and then creating a federated table named Customer. Note that the federation distribution key of cid is associated with the federation table column of CustomerID through the FEDERATE ON clause:

CREATE FEDERATION CustomerFederation(cid BIGINT RANGE)
GO

USE FEDERATION CustomerFederation(cid = 0) WITH RESET, FILTERING=OFF
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Customer](
[CustomerID] [bigint] NOT NULL,
[Title] [nvarchar](8) NULL,
[FirstName] [nvarchar](50) NOT NULL,
[MiddleName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NOT NULL,
[Suffix] [nvarchar](10) NULL,
[CompanyName] [nvarchar](128) NULL,
[SalesPerson] [nvarchar](256) NULL,
[EmailAddress] [nvarchar](50) NULL,
[Phone] [nvarchar](25) NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
[CustomerID] ASC
) 
)FEDERATED ON (cid = CustomerID)

The rows within a federated table are automatically distributed across all member databases within a federation. The member that a given row resides in depends on the value of the column that the table is federated on. Each federation member covers a specific range of values for the federation key, and will contain rows where the federated column value is within the range covered by the member.

Federated tables are included in operations such as SPLIT and DROP. For example, performing a SPLIT operation will result in a federation member splitting into two new databases. The rows of any federated table(s) contained within the member will be split also, and the data will reside in the new members after the SPLIT operation completes. This happens automatically and is transparent to any application using the federation.

Federated tables have the following limitations:

• The federation column of the federated table can only contain data that confirms 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 are stored as per reference tables in well-known system tables such as sys.objects. However federation specific properties are stored in sys.federated_table_columns (Windows Azure SQL Database).

Reference tables are tables created without the FEDERATE ON clause, and 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 hierarchy ID 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 hierarchy ID with federations.

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