Thanks Trent, points taken.

Edhy Rijo

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

This isn't the issue.  The issue is not when creating the PK, but rather when performing queries on the data.  In VFP we used to use Integer PKs which was extremely frustrating because they did not have the AutoInc stuff when we started (and their AutoInc is nowhere in the same universe as SQL Server).  However, since we used Integer PKs life did get much better once we got on SQL Server and did not have to convert from GUIDs to Integer PKs.

But back to the original point, the issue is not when creating the PK.  If you want to use a GUID PK, don't let the server assign it, just call the System.Guid.NewGuid() method in the SetDefaultValues of the business object versus relying on the server.  It is basically impossible to get two GUIDs of the same uniqueness anyway.

When using identiy fields in SQL Server, the BO has the ability to automatically retrieve the PK once a new record is created, which I am sure that you have already discovered.  So the issue isn't the whole NextId() thing that you faced in VFP, but rather when you start to query the data to retrieve records within the DB.

Hope that makes sense

Very interesting performance information about GUIDs which is a concern as we were looking to migrate to GUIDs from int PKs.

We have a legacy system that involves many distributed databases per customer. All databases must be updatable even if the WAN connection is down. As a result, our int PKs must be globally unique which is why we have to update the PKs from the application. We had planned on migrating to a new database model that used GUIDs but these performance figures are worrying.

Unfortunately, our PKs must be globally unique across all databases and other than the performance problem, GUIDs looked the easiest option for an automatic solution rather than our manual int solution.

In your case I would probably go with GUIDs so that you do not have to engineer the global PKs.  When weighing the costs between the two the ease of the GUIDs would win out in my eyes as we have tried to engineer the other behavior in more than one instance and it always ends up out of sync at some point which causes issues.  This would fall under the replication category that Paul was talking about earlier.

I think you are right Trent. Whatever way we have tried to engineer it, it still results in conflicts somewhere down the line. Of course, that leaves us with a real headache as we have a large no of customer databases that need to be converted from the old legacy database running with int PKs to a revised database with GUIDs PK.

Hi Aaron,

I have a VFP application installed in 17 branches which synchronize to the main office.  My solution was to used GUID PK and a BranchID field, the GUID works just find and in the main office where all the repository is there has not been performance issues because all the fields are properly indexed and the generation of reports is normal. 

I should note that the synch process is one way only from branches to the main office and that may not be your case.  So if I would have to do this project in SF I would need to re-think the logic since instead of synchronizing I would have all branches connected to the main office database using SF Enterprise server, but still using the GUID PK.

Edhy Rijo

Hi Edhy,

That is good to know. We have tested with GUIDs to a limited degree and it looked okay but our big problem will be converting legacy databases to a new format. Some of the databases are literally in use 24x7x365. But I guess that problem is for another day.

 Guys,

If you want to use GUID's then you definately will want to create the guid sequentially. using the Guid.NewGuid function will create a truly random unsequential guid which well cause performance to suffer. By creating a sequential guid you are ensuring that records are inserted at the end thus keeping page splits and index fragmentation to a minimum.This is primarily what causes perfomance problem's when people use guid's as primary keys.Using this approach you will approach insert and select perfomance that is much closer to using ints. You still will have the usual drawbacks of using guids they are ugly and big etc. There are several articles that detail this in more depth just google sequential guids.

I have 17 offices that I converted\converting from Foxpro 2.6. I had a very elaborate data merge process for consolidating data at corporate office that ran all night for several hours. I now use Sql merge replication and guids to great effect. One thing that is a business requirment for me is the abilitly for an office to function without an UP network connection to corporate thus a client server type of environment would not work.

I have not had any perfomance issues so far, Like anything else it always comes down to making a decision based on your unique business requirements.

Below is a code snippet to generate a SEQUENTIAL Guid and to add it in the default values of you base BO.

Wow Paul, thanks for sharing this info with us.

My next VFP conversion project will definately make use of this code since the use of a GUID PK is a must.

About the above comment, would you mind explaining a bit more about how to use SQL merge replication I also have those needs for my next conversion project.

Edhy Rijo