Question on Identity columns in 2005.

Howdy all. I used to frequent this forum and hope all has been well with
Paul, Hillary, and any other regulars I may have missed. Anyways, I just set
up Transaction, Immediate Updating replication on two 2005 boxes,
replicating the adventureWorks DB. One table in particular
(Person.AddressType) contains an Identity coulmn, and I did nothing to alter
it in any way. Anyways, once replication was fully configured, I inserted a
row into the table on the Subscriber, and it was assigned Identity value
20009. Questions:
1. It's been a while since I've done anything with Immediate Updating stuff,
but it seems to me that in 2000 I would have had to manually configure the
Identity value on the Subscriber box to use a different range of Identity
values than on the Publisher, right?
2. I looked on the Subscriber table Identity column, expecting the Seed to
be 20009, but it's not, it's 1! How can it be getting the value of 20009 if
the Seed is still set to 1?
TIA, ChrisR
Hi Chris - good to see you're back here!
The automatic identity range management is one of the defaults that has
changed across versions.
The seed and increment is the same behaviour though across versions. This
really refers to the table creation on the publisher. Run DBCC
CHECKIDENT(tablename) at the subscriber to get the new value (or look at the
check constraints on the subscriber's table). I'm guessing that it made
sense that it remains as the original because the new one would at some time
become out of date anyway when a new range is requested, so the easiest
solution was to just take the old table script.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul. But why is it "ignoring" the value of 1 that I see in
Management Studio?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23SpgBYBMHHA.2140@.TK2MSFTNGP03.phx.gbl...
> Hi Chris - good to see you're back here!
> The automatic identity range management is one of the defaults that has
> changed across versions.
> The seed and increment is the same behaviour though across versions. This
> really refers to the table creation on the publisher. Run DBCC
> CHECKIDENT(tablename) at the subscriber to get the new value (or look at
> the check constraints on the subscriber's table). I'm guessing that it
> made sense that it remains as the original because the new one would at
> some time become out of date anyway when a new range is requested, so the
> easiest solution was to just take the old table script.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
|||The idenity value is being reset on initialization using DBCC CHECKIDENT, so
the defined value is now 'meaningless'.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks Paul!
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eWuRb7BMHHA.4244@.TK2MSFTNGP04.phx.gbl...
> The idenity value is being reset on initialization using DBCC CHECKIDENT,
> so the defined value is now 'meaningless'.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>