RE: Making an application that uses identity keys occassionally connected

[Bill McCarthy]

Hi,

I think you both took "need to deal with" to mean you have to "write code to
deal with".  A one in E38 or there chance doesn't mean you have to have E38
records for it to happen. Think of a dice: long term probability tells us it
is one in six of any particular number yet that number can come up on the
very first throw (oh craps). "Dealing" with that means looking at what
damage can happen. If it's just one customer transaction failing, that's
probably acceptable to most people. If it is someone's details being
completely over written with no audit trail for recovery then that's
probably not acceptable in a lot of cases. You may just let it throw an
exception, but you shouldn't let it blindly over-write.

|-Original Message-
|From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-
|boun...@ozdotnet.com] On Behalf Of Craig van Nieuwkerk
|Sent: Monday, 6 February 2012 12:03 PM
|To: ozDotNet
|Subject: Re: Making an application that uses identity keys occassionally
|connected
|
|I am sure GUID collision detection code would be a good example of YAGNI.
|
|
|On Mon, Feb 6, 2012 at 11:56 AM, Heinrich Breedt 
|wrote:
|
|
|   I reckon I will skip writing code that has a 1 in 10^38 chance of
being
|needed
|
|   On Feb 6, 2012 10:33 AM, "Bill McCarthy"
| wrote:
|
|
|   The problem with guids is they are not guaranteed to be
unique:
|there's a
|   really large probability that they are unique. You still
need to deal
|with
|   possible collisions. I'm guessing that NEWSEQUENTIALID is
|probably less
|   unique than machine generated guids.
|
|
|   |-Original Message-
|   |From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-
|   |boun...@ozdotnet.com] 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
|<http://www.codeproject.com/Articles/32597/Performance-Comparison-
|%7CIdentity-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
|
|
|
|

RE: Making an application that uses identity keys occassionally connected

[Greg Low (GregLow.com)]

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: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com]
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 

RE: Making an application that uses identity keys occassionally connected

[David Ames]

GUIDs (even by newSequencialID) are 4 times are large as INT's and if you are 
using them as the Clustering Key as well as the primary key, then all of your 
non-clustered indexes are bigger then they need to be too.

Kim Tripp probably says it best with her article: 
http://sqlskills.com/BLOGS/KIMBERLY/post/Disk-space-is-cheap.aspx

I have used sequential guid's in the past & if there is a lack of good 
candidate for the clustering key on the table, we put an int/identity on the 
table purely to serve as the clustered index (ie, the logical DB model does not 
use the int/identity at all).

The down side is that you are "wasting" your clustered index in order to make 
other indexes smaller.

Dave


From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] 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

RE: Making an application that uses identity keys occassionally connected

[Heinrich Breedt]

V4 guid's don't use nic ids or timestamps anymore
On Feb 6, 2012 11:04 AM, "Greg Keogh"  wrote:

> >The problem with guids is they are not guaranteed to be unique:
>
> Hang on! I thought that was their major selling point. They are supposed to
> be sufficiently sparse and random over the 128 bits that the chance of
> collisions is really, really vanishingly small.
>
> NEWSEQUENTIALID is supposed to re-seed itself randomly on each boot. I
> haven't observed or tested this, has anyone? If it does work this way then
> perhaps there is a greater chance of contiguous clumps of Guids overlapping
> from different sources, but I'll bet that's still miniscule. Oh but more,
> your NIC ID is part of the bits so that would prevent clashes from
> different
> machines.
>
> Greg
>
>
>

RE: Making an application that uses identity keys occassionally connected

[Greg Keogh]

>The problem with guids is they are not guaranteed to be unique:

Hang on! I thought that was their major selling point. They are supposed to
be sufficiently sparse and random over the 128 bits that the chance of
collisions is really, really vanishingly small.

NEWSEQUENTIALID is supposed to re-seed itself randomly on each boot. I
haven't observed or tested this, has anyone? If it does work this way then
perhaps there is a greater chance of contiguous clumps of Guids overlapping
from different sources, but I'll bet that's still miniscule. Oh but more,
your NIC ID is part of the bits so that would prevent clashes from different
machines.

Greg

Re: Making an application that uses identity keys occassionally connected

[Craig van Nieuwkerk]

I am sure GUID collision detection code would be a good example of YAGNI.


On Mon, Feb 6, 2012 at 11:56 AM, Heinrich Breedt
wrote:

> I reckon I will skip writing code that has a 1 in 10^38 chance of being
> needed
> On Feb 6, 2012 10:33 AM, "Bill McCarthy" 
> wrote:
>
>> The problem with guids is they are not guaranteed to be unique: there's a
>> really large probability that they are unique. You still need to deal with
>> possible collisions. I'm guessing that NEWSEQUENTIALID is probably less
>> unique than machine generated guids.
>>
>>
>> |-Original Message-
>> |From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-
>> |boun...@ozdotnet.com] 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<http://www.codeproject.com/Articles/32597/Performance-Comparison-%7CIdentity-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
>>
>>
>>

RE: Making an application that uses identity keys occassionally connected

[Heinrich Breedt]

I reckon I will skip writing code that has a 1 in 10^38 chance of being
needed
On Feb 6, 2012 10:33 AM, "Bill McCarthy" 
wrote:

> The problem with guids is they are not guaranteed to be unique: there's a
> really large probability that they are unique. You still need to deal with
> possible collisions. I'm guessing that NEWSEQUENTIALID is probably less
> unique than machine generated guids.
>
>
> |-Original Message-
> |From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-
> |boun...@ozdotnet.com] 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
>
>
>

RE: Making an application that uses identity keys occassionally connected

[Bill McCarthy]

The problem with guids is they are not guaranteed to be unique: there's a
really large probability that they are unique. You still need to deal with
possible collisions. I'm guessing that NEWSEQUENTIALID is probably less
unique than machine generated guids.


|-Original Message-
|From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-
|boun...@ozdotnet.com] 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

RE: Making an application that uses identity keys occassionally connected

[Greg Keogh]

Folks, most people here seem to dislike Guids as primary keys. The article
  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 

RE: Making an application that uses identity keys occassionally connected

[Kirsten Greed]

Tony mentions 

>> Of course, because you can synchronise with the single-source-of-truth in
a batch anyway, both have strategies that will work. If you choose guids, I
think you need to separate out the data that you're adding from the client
vs reference data, so you don't make the mistake of implementing guids for
all tables, or ints for all tables and allowing clients to add reference
data using the same mechanism when it is unnecessary or even undesirable.

 

And Greg Low mentions on SQLDownUnder

 

>> 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.

 

I am having trouble thinking through how this would work.  Does this
solution use a framework like the sync framework ?

The scenario is a business application (customers, sales, inventory etc) but
users will want it to work the same wherever they are.

 

Greg Kennedy thinks that the sync framework will "cracks a fruity" if the
guid is not the Primary Key. ( see his email  sent Sat 11:28 am elaborating
on this)

 

Thanks

Kirsten

  _  

From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com]
On Behalf Of Tony Wright
Sent: Saturday, 4 February 2012 5:49 PM
To: g...@greglow.com; 'ozDotNet'
Subject: RE: Making an application thatuses identity keys
occassionallyconnected

 

I find that these sorts of discussions often come out of standardisation
meetings where people say stuff like "should we make all our primary keys
guids or ints" then make a broad ruling and dev teams have to live with the
consequences. Remember that one size does not have to fit all. 

 

Of course, because you can synchronise with the single-source-of-truth in a
batch anyway, both have strategies that will work. If you choose guids, I
think you need to separate out the data that you're adding from the client
vs reference data, so you don't make the mistake of implementing guids for
all tables, or ints for all tables and allowing clients to add reference
data using the same mechanism when it is unnecessary or even undesirable.

 

I don't need to repeat performance issues info about guids, but I have one
more comment to make that many people overlook, and that is:

 

>From a developer's point of view, GUIDs suck. 

 

With guids, if you have your query analyser window open and want to run a
quick query on a table to get a particular row, you have to have a valid
guid, which means you have to look up your guid then copy or rewrite whole
guid string into your query. Far more prone to errors. Therefore, with
Guids, cutting and pasting is necessary most of the time. 

 

With ints, it's really easy, because you can often remember the ids of
reference data after a while. With ints, most of the time, no cutting and
pasting is necessary. It is far easier to remember an int when crossing
between development and debugging in the query analyser. It's far easier
when you're profiling to see an int and type it straight into a query on
another screen.

 

Also think of keystroke analysis and how much extra you need to do to work
with Guids, such as requerying the database because it's too hard to
remember a guid, identifying the record you want, honing the mouse, copying
the guid, selecting your destination window, honing the mouse over the
destination query, pasting the guid in the correct location. All of that
instead of using a simple int!

 

So if you want your team to be more efficient in their development, work out
a strategy using ints and enjoy your jobs a little better. 

 

Regards, Tony Wright

Hazaa

 

From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com]
On Behalf Of Greg Low (GregLow.com)
Sent: Saturday, 4 February 2012 4:43 PM
To: 'ozDotNet'
Subject: RE: Making an application that uses identity keys
occassionallyconnected

 

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:   www.sqldownunder.com

 

From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com]
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() 

Re: Making an application that uses identity keys occassionally connected

[noonie]

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  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
>
> ** **
>
> ** **
>
> ** **
>

Re: Making an application that uses identity keys occassionally connected

[Greg Kennedy]

I think the sync framework will crack a fruity if the Guid is not the PK.

On Thu, Feb 2, 2012 at 8:50 AM, Greg Keogh  wrote:

> Kirsten has lots of tables already created with traditional PK IDENTITY
> columns, so I’m guessing that converting the PKs would be a nightmare.
> Perhaps a compromise is the answer: add an indexed Guid column to those
> tables that might find it useful in sync processing. That Guid is
> effectively “stamped” onto the row and never changes even if the INT PK
> does. I did this years ago to some of my own tables and they’re still in
> use and the Guids are used by external applications to reliably and
> unambiguously find rows.
>
> ** **
>
> However, can Sync Framework make use of Guid columns that are not the
> actual PK?
>
> ** **
>
> Greg
>
> ** **
>

RE: Making an application that uses identity keys occassionally connected

[Greg Keogh]

Kirsten has lots of tables already created with traditional PK IDENTITY
columns, so I'm guessing that converting the PKs would be a nightmare.
Perhaps a compromise is the answer: add an indexed Guid column to those
tables that might find it useful in sync processing. That Guid is
effectively "stamped" onto the row and never changes even if the INT PK
does. I did this years ago to some of my own tables and they're still in use
and the Guids are used by external applications to reliably and
unambiguously find rows.

 

However, can Sync Framework make use of Guid columns that are not the actual
PK?

 

Greg

 

Re: Making an application that uses identity keys occassionally connected

[David Connors]

Depending on your use cases and scale requirements, GUIDs a good choice.

An engineer at one of the companies I work with did a good chunk of
research and we resolved to use integers for our use cases:
http://www.google.com.au/webhp?ion=1#sclient=psy-ab&q=guids%20index%20fragmentation


My $0.02 is that if the scale of the data you're storing is low and you
don't care about performance, use GUIDs.

Otherwise I'd consider including a unique identifier for
the occasionally connected client as a part of the key.

On Thu, Feb 2, 2012 at 7:04 AM, Greg Kennedy  wrote:

> +1 for guids. I use them as PK in a sync framework app.
>
>
> On Thu, Feb 2, 2012 at 6:59 AM, David Ames  wrote:
>
>> ** **
>>
>> If your only talking a small number of clients you can play with
>> seed/increment values.
>>
>> ** **
>>
>> Eg:
>>
>> Node 1: Seed = 1, Increment = +2
>>
>> Node 2: Seed = 2, Increment = +2
>>
>> Node 3: Seed = -1, Increment = -2
>>
>> Node 4: Seed = -2, Increment = -2
>>
>> ** **
>>
>> ** **
>>
>> You can also assign ranges (and run the risk of running out of numbers in
>> the range)
>>
>> Node 1, Seed = 1
>>
>> Node 2, Seed = 100,000
>>
>> Node 3, Seed = 200,000
>>
>> Node 4, Seed = 300,000
>>
>> ** **
>>
>> ** **
>>
>> You could also use a GUID as the PK, set default = newSequentialid () to
>> keep fragmentation down.  
>>
>> ** **
>>
>> Dave
>>
>> ** **
>>
>> ** **
>>
>> ** **
>>
>> ** **
>>
>> *From:* ozdotnet-boun...@ozdotnet.com [mailto:
>> ozdotnet-boun...@ozdotnet.com] *On Behalf Of *Kirsten Greed
>> *Sent:* Thursday, 2 February 2012 7:52 AM
>> *To:* 'ozDotNet'
>> *Subject:* Making an application that uses identity keys occassionally
>> connected
>>
>> ** **
>>
>> 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
>>
>> ** **
>>
>> ** **
>>
>> ** **
>>
>
>


-- 
*David Connors* | da...@codify.com | www.codify.com
Codify Pty Ltd
Phone: +61 (7) 3210 6268 | Facsimile: +61 (7) 3210 6269 | Mobile: +61 417
189 363
V-Card: https://www.codify.com/cards/davidconnors
Address Info: https://www.codify.com/contact

Re: Making an application that uses identity keys occassionally connected

[Greg Kennedy]

+1 for guids. I use them as PK in a sync framework app.

On Thu, Feb 2, 2012 at 6:59 AM, David Ames  wrote:

> ** **
>
> If your only talking a small number of clients you can play with
> seed/increment values.
>
> ** **
>
> Eg:
>
> Node 1: Seed = 1, Increment = +2
>
> Node 2: Seed = 2, Increment = +2
>
> Node 3: Seed = -1, Increment = -2
>
> Node 4: Seed = -2, Increment = -2
>
> ** **
>
> ** **
>
> You can also assign ranges (and run the risk of running out of numbers in
> the range)
>
> Node 1, Seed = 1
>
> Node 2, Seed = 100,000
>
> Node 3, Seed = 200,000
>
> Node 4, Seed = 300,000
>
> ** **
>
> ** **
>
> You could also use a GUID as the PK, set default = newSequentialid () to
> keep fragmentation down.  
>
> ** **
>
> Dave
>
> ** **
>
> ** **
>
> ** **
>
> ** **
>
> *From:* ozdotnet-boun...@ozdotnet.com [mailto:
> ozdotnet-boun...@ozdotnet.com] *On Behalf Of *Kirsten Greed
> *Sent:* Thursday, 2 February 2012 7:52 AM
> *To:* 'ozDotNet'
> *Subject:* Making an application that uses identity keys occassionally
> connected
>
> ** **
>
> 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
>
> ** **
>
> ** **
>
> ** **
>

RE: Making an application that uses identity keys occassionally connected

[David Ames]

If your only talking a small number of clients you can play with seed/increment 
values.

Eg:
Node 1: Seed = 1, Increment = +2
Node 2: Seed = 2, Increment = +2
Node 3: Seed = -1, Increment = -2
Node 4: Seed = -2, Increment = -2


You can also assign ranges (and run the risk of running out of numbers in the 
range)
Node 1, Seed = 1
Node 2, Seed = 100,000
Node 3, Seed = 200,000
Node 4, Seed = 300,000


You could also use a GUID as the PK, set default = newSequentialid () to keep 
fragmentation down.

Dave




From: ozdotnet-boun...@ozdotnet.com [mailto:ozdotnet-boun...@ozdotnet.com] On 
Behalf Of Kirsten Greed
Sent: Thursday, 2 February 2012 7:52 AM
To: 'ozDotNet'
Subject: Making an application that uses identity keys occassionally connected

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