Creating an Azure SQL federation sharded off last digit of INT/LONGINT?

Question

Let say a table has a primary key (CustId) of type int or longint. We would like to shard this table into a federated table depending on whether the OrderId is odd or even. i.e.

Federated 'primary key' = (Fed_key, CustId) where OrderId is of type int or longint and Fed_key = 0 if CustId==even, 1 if CustId==odd.

For example:

CustId = 1234 => Federated 'primary key' = (0,1234) 

CustId = 6789 => Federated 'primary key' = (1,6789)

This basically gives us 2 federation members (aka partitions). Later on we may group Fed_key into (1,3,5), (2,4,6) and (7,8,9,0) for additional partitions. We don't think we'll need over 5 partitions.

Question: How do I express the above logic to Azure SQL? I guess it would need to be done during federation creation or federated table creation.

score 0 · Answer 1 · answered Mar 16 '13 at 06:16

0

from the docs(link):

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

besides, assuming a customer has multiple orders, do you really want to query x number of federation members to get all the orders?

answered Mar 16 '13 at 06:16

JuneT

7,840
2
15
14

You are right, I noted the wrong column. Long day. It's sharded over CustId. – DeepSpace101 Mar 16 '13 at 06:21

score 0 · Accepted Answer · answered Mar 16 '13 at 06:26

First, we went from INT/LONGINT identity to GUIDs/ uniqueidentifiers for the non-sharded primary key. The GUIDs are created in the application tier. Within the application tier itself, we pick the last nibble of the guid and decide the partitionId.

Now the PartitionId, CustId is the Federation wide primary key and the application presents this to the database tier during any transaction. This has been tested to work.

Creating an Azure SQL federation sharded off last digit of INT/LONGINT?

2 Answers2