none
SQL Azure Federation - Identity Column RRS feed

  • Question

  • I go to the Azure management portal and create a SQL Federation with distribution data type being bigint. I then create multiple federation members. When I write to any of the members using EntityFramework, after the first row is inserted, I get a duplicate primary key exception. Is there a way to have SQL Azure auto-increment the primary key? I don't want to manually assign an ID to each row.

    Friday, September 7, 2012 11:15 PM

Answers

  • Hi,

    Yes, there are page splits but since the writes have to be commited by 2 of the 3 servers theres always a network call and this call is much more "expensive" than page splits.


    If you found this post useful, Please "Mark as Answer" or "Vote as Helpful". Best Regards.

    Saturday, September 8, 2012 4:42 PM

All replies

  • Hi,

    Identity columns aren't supported in Federated Tables because there are no uniqueness and sequencial garantees across multiple databases. You can use Uniqueidentifier that is unique by definition and can even be generated at application level. If you need ordered IDs you can use datetime2 data type that has high resolution.


    If you found this post useful, Please "Mark as Answer" or "Vote as Helpful". Best Regards.

    Saturday, September 8, 2012 12:06 AM
  • Thanks. Are there any performance issues with having uniqueidentifier as the primary and clustered key?
    Saturday, September 8, 2012 1:47 AM
  • Hi,

    Yes, there are page splits but since the writes have to be commited by 2 of the 3 servers theres always a network call and this call is much more "expensive" than page splits.


    If you found this post useful, Please "Mark as Answer" or "Vote as Helpful". Best Regards.

    Saturday, September 8, 2012 4:42 PM