locked
Primary Key ID Generation RRS feed

  • Question

  • Dear All:

    I have a problem, it goes like this:

    I created an Entity, 
    when I add a record, a primary key will be generated and assgined to the records I added,
    I have checked from the database,
    the primary key cannot be null and its datatype is "uniqueidentifier"

    and now, if I would like to insert records manually, (means I want to manually insert records in DB),
    does any one know how to generate the primary ID??

    thank you very much!


     
    Thursday, January 8, 2009 3:54 AM

Answers

  • Yes, this does affect the system.  The CRM is using what the product team terms 'sequential guids'.  The indexes in the CRM tables are using clustered indexes.  If you do generate your own ids, they will be random (as you have seen), and in the end, the performance of the CRM could be negatively affected because instead of adding the row to the end of the table, SQL Server will have to add the rows to the middle of the table because the indexes are clustered.  Best practice for using Guids in SQL are to not use clustered indexes, and if the key is a unique key for the row, to also set the column as the rowid.  I don't know why the CRM uses sequential Guids.

     

    It should also be mentioned that you should never, under any circumstances, none-at-all, modify the SQL database directly.

     

    Shan McArthur

    www.shanmcarthur.net

     

    Monday, January 12, 2009 9:44 PM
    Moderator

All replies

  •  

    I am not sure about your actual req., but inserting records directly in CRM DB is not preferred.

    but if you want to, the syntax is

     

    newid()

     

    e.g. select newid()

    Thursday, January 8, 2009 4:30 AM
  • Hi Erikihsu,

     

    May i know what is the method used to add the record ? If you are using CRM SDK API WebService, in order to create the record, it should automatically generate the key for the particular record as the primary key.

     

    You can refer to the following article for references:

     

    http://msdn.microsoft.com/en-us/library/bb959400.aspx

     

    Hope this helps,

     

    hadi teo

    haditeo.wordpress.com

    Thursday, January 8, 2009 5:38 AM
  • Dears:

    Thanks for your replies!
    Your answers are very helpful.

    But I have another question,
    the primary ID in my DB now has the same patterns,
    if i use the new_id() , the  ID patterns will change,
    does it affect the system?

    the example is like as follows:
      system generated primary key:
          8A10D37E-86DC-DD11-98B0-00089BB85CE6
          965A0028-87DC-DD11-98B0-00089BB85CE6
          5A0AEAFB-61DC-DD11-98B0-00089BB85CE6
     
      if i use new_id(), the sample is like
          FD3716BA-50F7-4E04-88D8-DB210E1BBF7A
          D1D18A63-37CF-4E2D-BFBA-5625F3CA1A39
         
    Thursday, January 8, 2009 6:37 AM
  •  

    Hi Erikihsu,

     

    Although these 3 values almost followed the same pattern :

      system generated primary key:
          8A10D37E-86DC-DD11-98B0-00089BB85CE6
          965A0028-87DC-DD11-98B0-00089BB85CE6
          5A0AEAFB-61DC-DD11-98B0-00089BB85CE6

     

    But actually those 3 are 3 different values. Writing directly to the SQL database of the Dynamics CRM is an un-supported. So it's preferable to use CRM SDK API to perform actions etc. The reason is because, there are workflows and plugins that will be triggered when the actions are triggered by CRM SDK API , while if you directly modify the CRM database, the workflows and plugins are not invoked.

     

    Regards,

     

    hadi teo

    haditeo.wordpress.com

    Friday, January 9, 2009 9:05 AM
  • for the entity that you have created, you dont need hand-made IDs... just look for the attribute that has "primary key" type from the customization of the entity.
    Monday, January 12, 2009 9:36 AM
  • Yes, this does affect the system.  The CRM is using what the product team terms 'sequential guids'.  The indexes in the CRM tables are using clustered indexes.  If you do generate your own ids, they will be random (as you have seen), and in the end, the performance of the CRM could be negatively affected because instead of adding the row to the end of the table, SQL Server will have to add the rows to the middle of the table because the indexes are clustered.  Best practice for using Guids in SQL are to not use clustered indexes, and if the key is a unique key for the row, to also set the column as the rowid.  I don't know why the CRM uses sequential Guids.

     

    It should also be mentioned that you should never, under any circumstances, none-at-all, modify the SQL database directly.

     

    Shan McArthur

    www.shanmcarthur.net

     

    Monday, January 12, 2009 9:44 PM
    Moderator