I see that my new best friend - the DDT - creates PK indexes as clustered.



Is there a good way to turn this off, either globally ( in source code ? ) or perhaps there is something I'm missing in the current interface ( writing this before installing 1.61 so if it's there already just point me to it )



I use a lot of UID PKs in tables I am converting from VFP and my understanding is a clustered index isn't a good idea.



TIA



Charles

I kind of geeked out on this one as GUIDs are such a part of my VFP life. PKs generated on the backend require a roundtrip to the server before the pk is available for fks in children.





My understanding is currently when you do a NEW() in a SF bizobj on a UID field the UID is generated client-side.



As Ben said, the random nature of UIDs means inserts require a lot of disc thrashing. But this problem has been solved for years by people using one of many flavors of sequential guids ( just google ) There is even a function on SQL 2005 newsequentialid()



http://www.sqlmag.com/Article/ArticleID/50164/Dont_Overlook_the_New_SQL_Server_2005_NEWSEQUENTIALID_Function.html



There are other third party algorithms - written for SQL 2000 before the new function - that use getdate() as part of the function which I think could be adapted to be used client-side and would therefore be sequential without the round trip.



My friend Scott Bellware has posted a really neat solution in his blog



http://codebetter.com/blogs/scott.bellware/archive/2006/12/27/156671.aspx



which will probably be the first thing I try.



Suggest if the MicroFour get a chance they take a look at it and consider implementing something like this in the framework.'



Now you have best of both worlds (except for the disk space part ) Client side surrogate keys that can be used with clustered indexes ( and replication )



Thoughts?

Yes, using the NewSequentialID() method is awesome, but if you want to use SF with GUIDs and have the server assign the values, you will have to use stored procedures.  There is not any way to retrieve the newly assigned value from the DB if you put NewID() or NewSequentialID() in the default values field... it has to be retrieved, stored and then explicitly inserted when you insert the record.  So, with SF, that means use sprocs for INSERT and make the PK's parameter an output parameter so it can be retrieved by the DbDataSourceItem and put back into the BO.