Quick Tip - Federations in SQL Azure: How to automatically maintain the federation distribution key in federated tables with FILTERING connections.

In federations, federations member contain part of the data that is in a federation. The range of data contained in a federation member depends on the layout of the partitioning you have in your federations. There could be 10s or 100s of federation members covering the full range of values of the federation key. When connecting to federation member, apps use the USE FEDERATION command. USE FEDERATION takes a federation name (f1 below), a federation distribution key name (id below) and a atomic unit value (the guid value in the example below).

    1:  use federation f1(id='82855FD2-BA4C-4F72-BFB5-78523741C5A8') 
    2:  with reset, filtering=off

USE FEDERATION provides 2 connection types.

#1 The unfiltering connection is just a regular connection to the whole federation member containing this atomic unit. This is no different than connecting to the db name of the federation member. In the example below, I express to connect to a GUID value, however when I query t1 for all rows, I get back all data that is in this federation member’s range.

    1:  use federation f1(id='00000000-0000-0000-0000-000000000000') 
    2:  with reset, filtering=off
    3:  go
    4:  select * from t1
    5:  go
 
 Returns: 
 
    1:  c1                                   c2
    2:  ------------------------------------ ------
    3:  08FFE74B-1BC0-460B-AA47-044E2DD7C0E0 b
    4:  732ABAAE-8727-4C49-B40B-8013DFB3C735 b

#2 The Filtering connection is a new concept that is available with federations. Filtering connection connect to a slice of a database that contains your atomic unit as opposed to the whole federation member (database). The filtering connection simply eliminates the need to express the federation key value in every query against federated tables. SQL Azure automatically injects the WHERE <federation_column>=federation_filtering_value(…) into all queries that target a federated table. This means the above example returns no rows since there is not rows present for '00000000-0000-0000-0000-000000000000’ in t1. If I connect to an existing value such as … I only get the rows that are in the atomic unit for t1 for my query. See the example below;

    1:  use federation f1(id='08FFE74B-1BC0-460B-AA47-044E2DD7C0E0') 
    2:  with reset, filtering=on;
    3:  go
    4:  select * from t1
    5:  go
 Returns: 
    1:  c1                                   c2
    2:  ------------------------------------ ------
    3:  08FFE74B-1BC0-460B-AA47-044E2DD7C0E0 b

If you establish a filtering connection to a federation member, we do provide the following handy function to return the value back to you in the federation member.

    1:  use federation f1(id='08FFE74B-1BC0-460B-AA47-044E2DD7C0E0') 
    2:  with reset, filtering=on
    3:  go
    4:  select federation_filtering_value('id') as filtering_value
    5:  go

Returns:

    1:  filtering_value
    2:  ------------------------------------
    3:  08FFE74B-1BC0-460B-AA47-044E2DD7C0E0

You can also use the function to maintain the federation distribution column in federated tables for DML statements such as INSERTs. In the example below, t1 is created with a default based on this function. once a filtering connection is established, insert statements only specify the value to insert to column c2 and value of c1 is maintained automatically for you by the default property on table t1. Here is a full end to end example;

    1:  use federation root with reset
    2:  go
    3:  create federation f1(id uniqueidentifier range)
    4:  go
    5:  select 'use federation f1(id='''+cast(newid() as varchar(100))+''') 
    6:  with reset, filtering=on'
    7:  go
    8:  use federation f1(id='82855FD2-BA4C-4F72-BFB5-78523741C5A8') 
    9:  with reset, filtering=off
   10:  go
   11:  create table t1(
   12:    c1 uniqueidentifier primary key default federation_filtering_value('id'), 
   13:    c2 nvarchar(100)) 
   14:  federated on (id=c1)
   15:  go
   16:  select 'use federation f1(id='''+cast(newid() as varchar(100))+''') 
   17:  with reset, filtering=on'
   18:  go
   19:  use federation f1(id='732ABAAE-8727-4C49-B40B-8013DFB3C735') 
   20:  with reset, filtering=on
   21:  go
   22:  -- value of c1 is set to the atomic unit value in the filtering connection
   23:  -- '732ABAAE-8727-4C49-B40B-8013DFB3C735'
   24:  insert into t1(c2) values('a')
   25:  go
   26:  select federation_filtering_value('id')
   27:  go
   28:  select 'use federation f1(id='''+cast(newid() as varchar(100))+''') 
   29:  with reset, filtering=on'
   30:  go
   31:  use federation f1(id='08FFE74B-1BC0-460B-AA47-044E2DD7C0E0') 
   32:  with reset, filtering=on
   33:  go
   34:  -- value of c1 is set to the atomic unit value in the filtering connection
   35:  -- '08FFE74B-1BC0-460B-AA47-044E2DD7C0E0'
   36:  insert into t1(c2) values('b')
   37:  go
   38:  select federation_filtering_value('id')
   39:  go
   40:  use federation f1(id='00000000-0000-0000-0000-000000000000') 
   41:  with reset, filtering=off;
   42:  go
   43:  select * from t1
   44:  go
 Returns: 
    1:  c1                                   c2
    2:  ------------------------------------ ------
    3:  08FFE74B-1BC0-460B-AA47-044E2DD7C0E0 b
    4:  732ABAAE-8727-4C49-B40B-8013DFB3C735 b

If you happen to connect using a unfiltering connection, federation_filtering_value() will return NULL as we do not have a known filtering value. That will cause violation due to NOT NULL requirement for federation columns so app will be responsible for specifying an explicit value. I expect bulk inserting and updating of data for example will always explicitly specify federation key value.

Note: This functionality will be in the final version of the product however this does not work on the preview bits just yet. If you are in the technology preview program for federations today, you will get this functionality when we refresh the build to a more recent build.