I certainly defer to you experience regarding using guids with newid()  in SF.  In vfp/vfe I would use guids because generating the key on the front end saved a trip to the server before populating child fks and I often had situations where tables were going to be merged or data moved around among numerous installations of a program.  But I do see the problems - and the problems regarding using UID with a clustered index are obvious. 

I am very impressed with the way SF seems to give the best of both worlds in one sense - handling the fk thing with integer keys with no problem. 

Does the newsequentialid() function in SQL 2005 change any of the thinking about considering use of UIDs?  would it be difficult to have newsequentialid() used to create UIDs on the back end? 

I'm pretty sure I'll be using Int keys in SF for the most part, but just wondered about the practicality of sequential UIDs ?

http://www.fotia.co.uk/fotia/DY.19.NewSequentialId.aspx

Thanks Trent, points taken.

Edhy Rijo

One word of caution, if you are not planning on using replication you should not use GUID primary keys!  They create a lot of additional overhead and dramatically slow down queries on the SQL side versus an integer value.  So if you are planning on your databases getting very large, then I would highly recommend against using a GUID.  There is definintely a time and place for GUIDs and Paul's application is one of those, but if you are creating GUIDs just to create a GUIDs PK, then I would strongly recommnend against doing this.  We have been spending a lot of time in T-SQL lately and working on very complex queries and tracing execution paths...when you have a GUID as a primary key versus an integer, especially on a large database, the query can execute as much as 100 times slower (yes times, not percent).  A query that took 300 ms went to 3 ms.  So as the database becomes much larger and this same query took 1 second it would go from being 1 second to 10 ms.  This is a huge difference!

By taking this type of logic into account, we turned a query taking 4.5 seconds the other day on a very large database into 22 ms....that was a happy day   We made an even larger impact when dealing with dates.  Instead of storing these fields that we will be testing on with >= or <= as a date, we stored them as ticks in a BigInt field.  This was the largest change in performance that we had seen thus far.  Databases deal with integer data better than strings when it comes to performance and parameters.  You won't have as large a hit on a PK field, but you don't want to box yourself into a corner either.

Hi Paul,

Thanks for the code must appreciated.  Of course I did not have it since I am currently not using GUID for the current project, but yes, I will use it.

Edhy Rijo

Paul answered the question as to why the field property exists in the DDT perfectly.  As for having a property that sets these properties, you really just need to create a base BO and inherit your BOs from the base BO with these properties set, this way you don't have to set them each time.

Note: You will need to be on 1.6.6 beta and load the attached business assembly into the GAC in order for this to work.  I had to add the Overridable tab to the PrimaryKeyIsAutoIncremented and PrimarykeyIsUpdatable properties.

Hi edhy,

Guids as primekeys work fine with SF.

The issue I had was because I was using my guid primkeys as a merge replications rowguid column, This caused some issues that required the addition of the PrimaryKeyis Updatable property.

So If you are not using merge replication with guid primary keys and have set the merge replications rowguid field to the Prime key then the PrimaryKeyisupdateable setting wouldnt matter.

Here is some logic I have in my base class BO to assign the PK which may help you out if you haven't added it yet.

Hi Paul,

Yes it does at least to me   In my VFP application all my PK where GUID based since the creation of the GUID was very easy and light and it helped me when doing some remote synch with VFP tables.  I still have some projects which will be re-done in SF and the inclussion of this functionality will allow me to still use those GUID fields.  A couple of weeks ago, I did a small test of this functionality and it does work like a charm!

Edhy Rijo

Bill,

They added the PrimaryKeyUpdatable to remove the primekey from the update statement that is generated by the datalayer.

So with PrimaryKeyUpdatable set to false when a record is added and an insert is done it includes the pk field in the insert statement, however when that same record is later updated the pk field will not be included in the update statement.

Now if you edit a record and did not change the value of you pk field and PrimaryKeyUpdatable is set to TRUE then when you save the update statement includes the pk fld and it is updated with the same value so no harm really comes from it.

The SF guys added this property at my request I am using guid primekeys and merge replication. I have the prime key set as the rowguid for replication, if you try to update the pk field even with the same value it throws a sql exception which makes perfect sense.

I guess you can look at PrimaryKeyUpdatable = IncludePrimaryKeyFieldInUpdateStatement. It is probably not a bad idea to set it to false and turning it on only when you explicitly need to update a pk value.

I hope that helps am makes sense.

Paul

Hi Aaron!

I won't be able to help you with the DDT thing.  I was wondering of you could enlighten me a bit, though.  It seems odd to me that anyone would want to make a primary key updateable (an exception would be one-time data migration, I suppose).  Could you provide a real-world example where this type of thing is the best solution for data storage and retrieval?  I use primary keys everywhere...not a one is updateable.

Thanks for helping my curiosity!
Bill

It's me again

My BO is mapped to the Database Deployment Toolkit. Is there a table or primary key property in the DDT that sets the BO to PrimaryKeyIsAutoIncremented to true and PrimaryKeyIsUpdatable = false? The DDT field property does not have auto incrementing enabled and "Primary Key is Updatable" is false. The table properties "Automated Primary Key Index Creation on Structure Saves" is true.

Just wondering if my DDT is wrong as I would ideally like all my BOs to default to PrimaryKeyIsAutoIncremented to false and PrimaryKeyIsUpdatable = true.

Thanks