SQL Azure SPLIT ON UNIQUEIDENTIFIER GUID

2019-07-23 16:29发布

问题:

Let's say there is an application generating random GUIDs corresponding to number of normalized records in a few tables. These records with GUID "tenant_id" need to be split into multiple federated members in SQL Azure. When the SPLIT AT command is issued, what ordering mechanism is used to split members at a specific point (tenant_id)? Is it similar to ORDER BY GUID_FIELD ASC/DESC resultset? Since GUIDs are generated randomly, what is the best way to create ranges with future splits?

Thank you

回答1:

GUIDs ranges are split according to their sort order in SQL Server - the same that is used for ORDER BY and indexes. See this blog post for more details on this: http://sqlblog.com/blogs/alberto_ferrari/archive/2007/08/31/how-are-guids-sorted-by-sql-server.aspx

If you are generating GUIDs randomly and you need to split, you should use the ordering definition for GUIDs to pick a point somewhere in the middle of the set of GUIDs in the member you are splitting (assuming you want to split in the middle).

If you want more control about what tenants go where, you could generate your own, "custom" GUIDs, but then you will of course lose the global uniqueness property that GUIDs have, unless you ensure globally unique generation of your "custom" GUIDs.

-- Hans Olav



回答2:

Essentially, split at splits a single federation into two. It relies on the distribution key (the key you passed in the federated on clause). For example, imagine you federate on age. Origianlly you have two federations: age from 0 to 40, and age from 41 to 80. Now you split the first federation into two parts: 0 to 20 and 21 to 40. SQL Azure will automatically organize the data to make sure each federation meets the range requirement. So yes, it is kind of like order by.

Usually federation is not used on GUIDs. Instead, it's used on some key that you have more control. Using GUID is fine, but you have the risk to unbalance the federations. One federation may contain a lot of data, while the other only a little.