Identity and timestamp are important pieces of functionality for many existing apps for generating IDs. Federation impose some restrictions on identity and timestamp and clearly we need alternatives for federations that can scale to the targets of scale federations hits. So I’ll dive into alternatives and options in this post.

Lets take a look at identity and timestamp first in detail to understand why they are not good fits in federations: Identity is an unique id generation scheme and timestamp is typically used in optimistic concurrency implementation. Digging a little deeper, identity promises to generate a linearly increasing value without gaps scoped to a table in a given database. It provides the ability to reseed and provides an easy way to ensure uniqueness for many apps. Timestamp similarly provides a uniquely increasing value for all updates scoped to the database. Federations use databases as its building block and in an elastic fashion thus change the scope of databases as federation is repartitioned. This means you may end up with duplicate values generated in 2 separate members that got merged together when identity or timestamp is used on columns. We could generate the values in the root and make them globally unique but we end up with a single choke point that literally will limit your throughput. so what to do?

There are a number of options for distributed systems.

- GUID/Uniqueidentifier as a unique id generation method: I strongly recommend using uniqueidentifier as a identifier. It is globally unique by definition and does not require funneling generation through some centralized logic. Unlike identity and timestamp, uniqueidentifiers can be generated at any tier of the app. With unique identifiers you give up on one property; ID generation is no longer sequential. So what If you’d like to understand the order in which a set of rows were inserted? Well that is easy to do in an atomic unit. You can use a date+time data type with high enough resolution to give you ordering: ex: datetime2 or datetimeoffset data types both have resolution to 1/1000000 fraction of a second. So these types have great precision for ordering events.

This is more of an academic topic and don’t expect many folks to try this but, I’ll still mention that I strongly trying ordering across atomic units. Here is the core of the issue; If you need to sort across AUs, datetimeoffset still may work. However it is easy to forget that there isn’t a centralized clock in a distributed system. Due to the random number of repartitioning operations that may have happened over time, the date+time value may be coming from many nodes and nodes are not guaranteed to have sync clocks (they can be a few mins apart). Given no centralized clock, across atomic units datetime value may not reflect the exact order in which things happened.

Well, how about the difficulty of partitioning over ranges of uniqueidentifiers? GUIDs are sortable so this is nothing new. It is also simple to figure out where to split a collection of guids for best distribution of your data. There is a great example that walks you through in this post on how to split with uniqueidentifiers are the federation columns. If you’ d like to understand how guids are sorted in s, don’t worry you really don’t have to but If you insist this post does a good job: How are GUIDs sorted by SQL Server.  

Last but not the least, many people have experiences that suggest GUIDs (uniqueidentifiers) are bad candidates for clustering keys given they will not be ordered and cause page splits, causing higher latencies and fragmentation? No so on SQL Azure. at least not to the degree you experience in on premise SQL Server. SQL Azure dbs give you 3 replicas and that means the characteristics of writes are very different compared to a single SQL DB without HA. In SQL Azure the write have to be confirmed by 2 out of the 3 copies thus are always a network level writes… A network write today is much slower in latency compared to what a page split would cause. Page split makes a smaller amount of this latency. There is an impact of doing more work with GUIDs no doubt. For example, operations that take 10ms with INT can take 12 ms with GUIDs but overall that is a good comprimise on latency as long as you can support more concurrent work and scale the system with other benefits of GUIDs. It is also true that, you do end up with some fragmentation with uniqueidentifier. However fragmentation is hard to completely get rid of and unordered inserts compared to deletes or expanding updates don’t cause as much fragmentation so my experience simply says clustering on uniqueidentifiers is no reason for worry. This is easy to try; simply run the following script and watch your latencies. Here is a quick test you can try: See if you can make an unordered insert like the case for GUIDs take longer over many inserts on Azure SQL DB: (I want to be clear that this argument does not apply to SQL Server. It is only true for Azure SQL DB)

use federation root with reset
go
drop table t1
drop table t2
go
create table t1(c1 int identity primary key, 
  c2 uniqueidentifier default newid(), 
  c3 char(200) default 'a')
create table t2(c1 int identity, 
  c2 uniqueidentifier default newid() primary key, 
  c3 char(200) default 'a')
go
-- MEASURE T1 
set nocount on
declare @s datetime2
set @s=getdate()
declare @i int
set @i=0
begin tran
while (@i<100000)
begin
     if (@i%1000=0)
          begin 
               commit tran
               begin tran
          end
     insert into t1(C2, C3) values(default,default)
     set @i=@i+1
end
commit tran
select datediff(ss,@s, getdate()) 'total seconds for t1'
go
-- MEASURE T2
set nocount on
declare @s datetime2
set @s=getdate()
declare @i int
set @i=0
begin tran
while (@i<100000)
begin
     if (@i%1000=0)
          begin 
               commit tran
               begin tran
          end
     insert into t2(C2, C3) values(default,default)
     set @i=@i+1
end
commit tran
select datediff(ss,@s, getdate()) 'total seconds for t2'
go

- Datetime2 for optimistic concurrency: Timestamp replacement is much easier to talk about; I recommend using datetime2 with its 1/1000000 of a second resolution instead of timestamp. Simply ensure that you generate ever time you update, you also update the modified_date and ensure to use that just like timestamp to compare before updates to detect update conflicts.

That said, we plan to enhance these functions to be meaningful for federations in future and will also remove some restrictions in members especially on reference tables. if you need any more details on these, you can always reach me through the blog.

Thanks!

-cihan