none
IMPORTANT: Change in Mobiles Services Tables - ID column is now 'string'

    General discussion

  • A number of scenarios require application control over the value of the table primary key (ID), for example ability to make it globally unique. As of Friday 2013/11/22, we updated the service so that newly created tables use string as a type of its ID column instead of int.

    Note: your existing applications and tables are not affected by this change. Only newly created tables will get the ID columns of type string.

    To work with the tables created after this update, you need our latest SDK release that supports IDs of the type string in addition to int. You also need to use the type ‘string’ for your item’s Id type. For example, 
     

    public class MyItem
    {
        public string Id { set; get; }
        public string Property1 { set; get; }
        public DateTime Property2 { set; get; }
    }

    If you are following instructions for a Mobile Services sample or a hands on lab that were written before this update and not updated to reflect this change, you may get a deserialization error. The sample likely tries to use the models with the property ID of type int with the newly created tables. Change the type of the Id property from int to string in the data models in the sample code to avoid the error.  Please report such sample/hands on lab on this forum so that we fix it.


    Saturday, November 23, 2013 4:45 AM
    Moderator

All replies

  • on the below Azure sample project

    http://www.windowsazure.com/en-us/develop/mobile/how-to-guides/work-with-xamarin-client-library/#inserting

    TodoItemAdapter.cs has a override method that will cause compile erro
    se the adapter is expecting a long return type. Is there any fix for that ? 



    public override long GetItemId(int position)
    {
    return this [position].Id;
       
    }
    Wednesday, November 27, 2013 8:44 AM
  • Hi Billy,

    We are in the process of updating the sample project. In the meantime, try this:

    public override long GetItemId(int position)
    {
       return position;
    }
    Paul

    Thursday, November 28, 2013 12:06 AM
    Moderator
  • Hello, Carlos,

    Thank you at least for writing about this. The change has been very hard to find documentation on.

    The "Id column, now a string" broke one famous third party vendor's controls that I have been using for Azure mobile data synchronization.

    Secondly, for those of us that would like to go back in and either modify the newly created table to have a Bigint type for Id, some documentation on creating a new table with a Bigint for Id using the command line tools (Azure CLI) would be nice.


    Daniel Maxwell

    Thursday, November 28, 2013 11:36 PM
  • Hi Daniel,

    You can create an "old-style" table (one with an integer id) using the following command with the Azure CLI:

    azure mobile table create --integerId <mobile service name> <table name>

    Hope this helps.


    Carlos Figueira

    Friday, November 29, 2013 11:35 PM
    Moderator
  • Carlos,

    These changes have brought about an issue in the online Database editor.  When a new Azure Mobile Service is created and you create a plain new table (in this I have named it 'Item'), when you attempt to add a row to that table in the online Database editor, it denies you and gives the error "Cannot add a row in the Table Data Editor because one or more columns are required but their SQL types are not supported in Table Data Editor. Use the Transact-SQL Editor to add a row."  This worked fine before these changes were rolled out.  Hoping this can be resolved soon!

    Cheers,

    Paul

    Saturday, November 30, 2013 5:38 PM
  • Sorry I'm not one of those who knows how to use command line tools. Is there a way to create an "old-style" table either in Visual Studio or Azure management portal?

    Rodger Campbell

    Sunday, January 05, 2014 11:43 PM
  • Current the CLI is the only client with the option to create a table with an integer Id. 

    You could also create a table in your SQL DB yourself, then create the table in the portal, in this case the portal will just see the table already exists and just expose it to you.

    What are you looking to do that requires the older table model?

    Wednesday, January 08, 2014 11:03 PM
    Moderator
  • I like the older table model as I can see clearly which item was added first, its easier to work with number rather than a string, also want to make my app work faster so having to transfer a huge long string as unique ID accross the web rather than a number seems more sensible.  Don't know why Microsoft did this, maybe they thought it would allow for bigger tables, the data in table of my apps won't get that big.

    Rodger Campbell

    Thursday, January 09, 2014 6:42 PM
  • Carlos - you're model shows the new Id column as camel-cased. What I've experienced to date (prior to this new change) is that my ID column must be all lower case for the SDK to work and update tables properly.

    Can you confirm whether this is still the case? Does the identity column on a table need to be all lower-cased (id) like it was previously? Or is it now camel cased (Id)? Thanks.

    Saturday, February 01, 2014 9:02 PM
  • It appears that the lower-cased identity column requirement is still in place. I just created a table directly from within Azure and the identity column gets created as "id." So if you're creating your tables in SQL and then "adding" those tables to Mobile Services you it appears you still need to make sure the "id" is all lower-cased.

    The classes in your code, however, can be contain a camel-cased "Id" value, as Carlos shows in his example above.

    Saturday, February 01, 2014 9:36 PM
  • Is there guidance on using the new string Id (for storing GUIDs) with respect to performance? I'm not a DBA and don't have a great handle on whether I should be concerned about performance when using the new string Id column as a primary key.
    Saturday, February 01, 2014 10:20 PM
  • I don't know about a guide, but from my own database design experience in sqlserver, I know there is a big performance penalty at least with Dell low end servers (5,000 USD aprox).

    Also I've worked with SAP Business One which uses a lot of varchar(20) primary keys. It is extremely slow when querying multiple tables.

    I would suggest using vartype bigint identity for the id which can hold 9,223,372,036,854,775,807 rows. If you need more rows consider dividing into multiple tables if you can. Also you will be saving space.

    Being able to use an int for a primary key was one of my main reasons to choose Azure Mobile Services instead of Parse. Thank you for still letting us use a bigint for the id, please don't change this!

    The new update also creates an insert update trigger that I cannot see what it does. (Guessing it creates the value for the id and updates the create and update dates) More unnecessary processing if we already using scripts. Windows Azure Mobile is super transparent vs other mobile services, lets keep it that way!
    Wednesday, February 12, 2014 3:46 AM