The problem with NEWSEQUENTIALID is that it fixes the wrong problem.
One of the main reasons for using GUIDs in the first place is so they can be generated in other layers of code (and potentially other servers) and then just thrown into the database. If you are going to the database to get a GUID of any type, you might as well get an INT instead. Regards, Dr Greg Low 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 fax SQL Down Under | Web: <http://www.sqldownunder.com/> www.sqldownunder.com From: [email protected] [mailto:[email protected]] On Behalf Of Kirsten Greed Sent: Saturday, 4 February 2012 3:39 PM To: 'ozDotNet' Subject: RE: Making an application that uses identity keys occassionallyconnected Noonie Were you using NEWSEQUENTIALID() in the app that had problems in production? David Amos also mentioned NEWSEQUENTIALID() to keep fragmentation down - but I missed it's importance. Bill - thanks for the info and link. I am thinking that the problem with 2 part keys is that it's a bigger re-write than changing primary keys. I am sure to forget to add the 2nd key in places! Kirsten _____ From: [email protected] [mailto:[email protected]] On Behalf Of Bill Chesnut Sent: Saturday, 4 February 2012 3:20 PM To: ozDotNet Subject: RE: Making an application that uses identity keys occassionallyconnected Kirsten, One issue with GUID keys is that they are not one-up so with clustered indexes base on a GUID, inserting new records into a table typically can cause a page split, which is expensive, there is a different algorithm to generate a GUID that is always increasing so it acts more like an integer key. In SQL 2005 and above it is NEWSEQUENTIALID() and there is code to generate it in .net I think. Good article that compares all of these key types: http://www.codeproject.com/Articles/32597/Performance-Comparison-Identity-x- NewId-x-NewSeque Bill Chesnut BizTalk Server MVP Melbourne, Australia _____ From: Kirsten Greed [mailto:[email protected]] To: 'ozDotNet' [mailto:[email protected]] Sent: Sat, 04 Feb 2012 12:40:51 +1100 Subject: RE: Making an application that uses identity keys occassionallyconnected Hi Noonie Thanks for that info, I have hundreds of thousands of records in some of my tables and have been very happy with identity integers as primary keys so far. Kirsten _____ From: [email protected] [mailto:[email protected]] On Behalf Of noonie Sent: Saturday, 4 February 2012 12:05 PM To: ozDotNet Subject: Re: Making an application that uses identity keys occassionallyconnected Hi Kirsten, >From my experience, using GUIDs as surrogate primary keys works fine in development where sample data is only thousands of rows. It bites viciously in production where we have millions of rows and deep dependencies. Int keys are still surrogate keys but seem to offer better performance. How you do this will depend on what your occasionally connected applications are connecting to. If it is a reporting database that consolidates data from different sites then your solution would be simpler than where it shared the information across all the remote sites. All the suggestions mentioned, in this thread, are viable. I have used two-part keys (e.g site-id, item-id) in the past to good effect. More recently we have added non-key GUIDs for the central database to verify equality and, as we don't redistribute all the data to the remote databases, it's only used as a tie-breaker. The solution you choose will depend on the nature of the application and how much sync code you are willing to write. If you use a framework you are as much constrained by its limitations as you are freed from having to write code :-( -- Regards, noonie On 2 February 2012 07:51, Kirsten Greed <[email protected]> wrote: Hi All I am interested in making my application occasionally connected. My app uses SQL Server and the tables have identity keys (auto incrementing numbers) I can see this will be a problem if I have 2 databases on separate computers because they will both want to assign a new record to the same key. Indeed when I experimented with Microsoft Sync Framework 2, this is what happened. Any advice on a strategy forward? Thanks Kirsten __________ Information from ESET NOD32 Antivirus, version of virus signature database 6777 (20120108) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
