Thanks Greg I get the point about saving the round trip!
I am starting to hope I can use my existing database structure replicated at each site, with some kind of mapping system hosted in the cloud The mapping table would be something like GUID SiteID - one for each installation TableID - one for each table PKIntID - the identity field for the record at the site Is this what you were talking about when you said "The other main thing is that there often seems to be a presumption that you have to have your logical data model the same as your physical data model. You don't. For example, rather than having all your tables that have a customer ID having the GUID, you can always have ints or bigints all over the place, and just have one table that maps between them." ? Does SQL Server have some sort of in built process we could piggy back? Thanks Kirsten _____ From: [email protected] [mailto:[email protected]] On Behalf Of Greg Low (GregLow.com) Sent: Monday, 6 February 2012 2:16 PM To: 'ozDotNet' Subject: RE: Making an application that uses identity keysoccassionally connected Hi Greg, The point I was making is that the main reason for using GUIDs is so that the code that creates an object can assign an ID to it without having to reference a single allocator for IDs. I could have five servers and four apps and they can all happily create values and related objects that will then be able to be thrown into a single database some time later. This gets even worse where you have queues involved. The code generating the object might not even have access to the database. With a GUID, the code can generate an ID, then ship it across the queue to some other system to process it. Any ID generated by database misses the point. To use them, I usually run off to the database to get an ID before I continue. If I'm going to do that, what's the advantage of getting a NEWSEQUENTIALID rather than just an INT or BIGINT? It's just uglier for no benefit. The main aim of using GUIDs is to avoid being tied to some server that's the source of all IDs, and having to incur round-trip costs to it. 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 Greg Keogh Sent: Monday, 6 February 2012 11:28 AM To: 'ozDotNet' Subject: RE: Making an application that uses identity keys occassionally connected Folks, most people here seem to dislike Guids as primary keys. The article <http://www.codeproject.com/Articles/32597/Performance-Comparison-Identity-x -NewId-x-NewSeque> via Bill is quite sobering, showing that NEWID is a shocking performer, but INDENTIY and NEWSEQUENTIALID perform similarly well. After reading that I am unlikely to use NEWID again. I would still like to hear convincing arguments against NEWSEQUENTIALID. Noonie says his DBAs rejected them (why?). Tony hates looking at them in the debugger (that's not a convincing argument for me). Greg L says you might as well get an INT instead (more details?). I hope you'll agree that there are times when you want to give rows an immutable primary key. Will you also agree that an IDENTITY INT is not immutable because it can change when rows move across databases or when rows are reorganised or reloaded. If this is so, how on earth do you stamp your rows with an immutable key without using something like Guids? Greg __________ Information from ESET NOD32 Antivirus, version of virus signature database 6777 (20120108) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com
