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
