Repl problem with Identity Ranges?

I've given the automatic range on one of the tables for replication and I
entered
1000000 for publisher and 1000000 for subscriber.
When the table is replicated, the id column starts with 278418. Why does it
do this? does SQL know not to count? or am i missing something in
understanding?
I'm sorry. After some research, I found out that i missed something. Sorry
for the post.

repeating identity

When using Access - you had to be careful about the autoincrement feature. If you delete a record from a table ( autoincrement id=1000) -then compact/repair - then add a new record to that table - the autoincrement field will say 1000 - if that autoincrement value was used to uniquely identify something - it is no longer unique. This is all background for my question...

Does SQL 2000 do this also ? I must have read somewhere that it doesn't - since I have code that moves records around ( delete from one table - insert into other), but the other night, I was awakened by the thought that SQL2000 does the same as Access - i.e. repeating identity after compact/repair

Do I need to worry ?The only thing you need to worry about is if you happen to use theSET IDENTITY_INSERT ON command when inserting records.

This command will allow you to specify a value for the identity column when inserting a record instead of allowing SQL to assign one. And SQL won't know that your assigned numbers are already used and could attempt to reuse them later. This would only happen if you were sloppy in your assignment -- and it is not likely you would need to ever use the SET IDENTITY_INSERT anyway.

FWIW,
Terri|||thank you everyone : ) After re-reading my original post - I thought maybe I should reword it , just to make sure.

I have a table for service calls that has the identity field used for the service call number.
When the call is closed, I delete it from the open service call table - and insert it into the closed calls table.

If the call was number 1000 - open service calls highest identity will be 999.

when the next new call is created - will SQL assign 1001 or 1000 ( access would set new rec to 1000 - if a compact was done )

thanks again|||I'm not 100% clear...but if I am following you...

If your service call was 1000, and then was closed, and you then deleted that service call record from your table, the next identity assigned would be 1001. SQL Server will not reuse 1000.

With 2 exceptions
-- you explicitly insert a record with the identity field set to 1000
-- you reset the identity seed to 1000

Under normal circumstances without you doing anything funky like that, you are fine. SQL Server does not behave like Access.

Terri|||thanks again

Renumbering an Identity Column

Is there any way to renumber an identity column?
Due to rows being deleted etc., I have identity columns that go 1, 3, 22, 23
etc.
Is there a way to renumber them 1, 2, 3, 4 etc.?
Hi,
1. Take the data out to a temp table
2. Truncate the original table or use DBCC CHECKIDENT with reseed option to
reset the value back to 1
3. Load the data back to the table. Please do not include the identity
column while insertion. This will generate the
values in order .
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uUj9uRSKEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Is there any way to renumber an identity column?
> Due to rows being deleted etc., I have identity columns that go 1, 3, 22,
23
> etc.
> Is there a way to renumber them 1, 2, 3, 4 etc.?
>
|||"Keith" <@..> wrote in message news:uUj9uRSKEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Is there any way to renumber an identity column?
> Due to rows being deleted etc., I have identity columns that go 1, 3, 22,
23
> etc.
> Is there a way to renumber them 1, 2, 3, 4 etc.?
As Hari showed, yes there is a way.
Remember this violates the rules of database integrity
|||this implies that you are placing meaning on the numbers.
this is bad design.
(For what it's worth)
Greg Jackson
PDX, Oregon
|||It doesn't necessarily imply that he's placing meaning on the numbers. He
might not even have designed the database!
Compacting ID columns can be an important maintenance task, particularly to
avoid overloading ID columns that use smaller int types, such as smallint.
Simple example:
(1) You've got a table that uses smallint for the identity datatype. (3rd
party app & you can't change this)
(2) You have only 1 row, but they identity value is 32766, so you're running
out of space - the ID column will only accept one more row. (This happens in
cases where you archive data.)
(3) You need to compact the data so that you can fit more rows in the table
by re-assigning keys to smaller values.
(4) The only solution in this case is to do what Keith is asking..
This occurs often in apps that use identity, acquire lots of data and
perform regular archiving, leaving massive gaps in the ID ranges. It's also
not only common on SQL Server, it happens on all major DBMS.
Regards,
Greg Linwood
SQL Server MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...
> this implies that you are placing meaning on the numbers.
> this is bad design.
> (For what it's worth)
>
> Greg Jackson
> PDX, Oregon
>
|||Greg,
Good points.
But then you have to proliferate the new PK values to all child table foreign keys that refer to this table, yes? Sounds like an operation very susceptible to errors.
Thanks,
Dick
-- Greg Linwood wrote: --
It doesn't necessarily imply that he's placing meaning on the numbers. He
might not even have designed the database!
Compacting ID columns can be an important maintenance task, particularly to
avoid overloading ID columns that use smaller int types, such as smallint.
Simple example:
(1) You've got a table that uses smallint for the identity datatype. (3rd
party app & you can't change this)
(2) You have only 1 row, but they identity value is 32766, so you're running
out of space - the ID column will only accept one more row. (This happens in
cases where you archive data.)
(3) You need to compact the data so that you can fit more rows in the table
by re-assigning keys to smaller values.
(4) The only solution in this case is to do what Keith is asking..
This occurs often in apps that use identity, acquire lots of data and
perform regular archiving, leaving massive gaps in the ID ranges. It's also
not only common on SQL Server, it happens on all major DBMS.
Regards,
Greg Linwood
SQL Server MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> this implies that you are placing meaning on the numbers.
> PDX, Oregon
|||Hi Dick.
Yes - proliferating the keys down to any referencing foreign keys is a
requirement, but this is not susceptible to errors as long as it's planned
well.
If there is any maintenance window, this can be performed as part of regular
maintenance. If not, it needs to be performed transactionally and scheduled
regularly enough that this is not a large burden on the system.
Regards,
Greg Linwood
SQL Server MVP
"Dick" <deacdb2@.hotmail.com> wrote in message
news:8F1D7D0A-5669-4A9E-8434-EA2101D3CE9F@.microsoft.com...
> Greg,
> Good points.
> But then you have to proliferate the new PK values to all child table
foreign keys that refer to this table, yes? Sounds like an operation very
susceptible to errors.
> Thanks,
> Dick
>
> -- Greg Linwood wrote: --
> It doesn't necessarily imply that he's placing meaning on the
numbers. He
> might not even have designed the database!
> Compacting ID columns can be an important maintenance task,
particularly to
> avoid overloading ID columns that use smaller int types, such as
smallint.
> Simple example:
> (1) You've got a table that uses smallint for the identity datatype.
(3rd
> party app & you can't change this)
> (2) You have only 1 row, but they identity value is 32766, so you're
running
> out of space - the ID column will only accept one more row. (This
happens in
> cases where you archive data.)
> (3) You need to compact the data so that you can fit more rows in the
table
> by re-assigning keys to smaller values.
> (4) The only solution in this case is to do what Keith is asking..
> This occurs often in apps that use identity, acquire lots of data and
> perform regular archiving, leaving massive gaps in the ID ranges.
It's also[vbcol=seagreen]
> not only common on SQL Server, it happens on all major DBMS.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
> news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...

Renumbering an Identity Column

Is there any way to renumber an identity column?
Due to rows being deleted etc., I have identity columns that go 1, 3, 22, 23
etc.
Is there a way to renumber them 1, 2, 3, 4 etc.?Hi,
1. Take the data out to a temp table
2. Truncate the original table or use DBCC CHECKIDENT with reseed option to
reset the value back to 1
3. Load the data back to the table. Please do not include the identity
column while insertion. This will generate the
values in order .
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uUj9uRSKEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Is there any way to renumber an identity column?
> Due to rows being deleted etc., I have identity columns that go 1, 3, 22,
23
> etc.
> Is there a way to renumber them 1, 2, 3, 4 etc.?
>|||"Keith" <@..> wrote in message news:uUj9uRSKEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Is there any way to renumber an identity column?
> Due to rows being deleted etc., I have identity columns that go 1, 3, 22,
23
> etc.
> Is there a way to renumber them 1, 2, 3, 4 etc.?
As Hari showed, yes there is a way.
Remember this violates the rules of database integrity|||this implies that you are placing meaning on the numbers.
this is bad design.
(For what it's worth)
Greg Jackson
PDX, Oregon|||It doesn't necessarily imply that he's placing meaning on the numbers. He
might not even have designed the database!
Compacting ID columns can be an important maintenance task, particularly to
avoid overloading ID columns that use smaller int types, such as smallint.
Simple example:
(1) You've got a table that uses smallint for the identity datatype. (3rd
party app & you can't change this)
(2) You have only 1 row, but they identity value is 32766, so you're running
out of space - the ID column will only accept one more row. (This happens in
cases where you archive data.)
(3) You need to compact the data so that you can fit more rows in the table
by re-assigning keys to smaller values.
(4) The only solution in this case is to do what Keith is asking..
This occurs often in apps that use identity, acquire lots of data and
perform regular archiving, leaving massive gaps in the ID ranges. It's also
not only common on SQL Server, it happens on all major DBMS.
Regards,
Greg Linwood
SQL Server MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...
> this implies that you are placing meaning on the numbers.
> this is bad design.
> (For what it's worth)
>
> Greg Jackson
> PDX, Oregon
>|||Greg,
Good points.
But then you have to proliferate the new PK values to all child table foreig
n keys that refer to this table, yes? Sounds like an operation very suscept
ible to errors.
Thanks,
Dick
-- Greg Linwood wrote: --
It doesn't necessarily imply that he's placing meaning on the numbers. He
might not even have designed the database!
Compacting ID columns can be an important maintenance task, particularly to
avoid overloading ID columns that use smaller int types, such as smallint.
Simple example:
(1) You've got a table that uses smallint for the identity datatype. (3rd
party app & you can't change this)
(2) You have only 1 row, but they identity value is 32766, so you're running
out of space - the ID column will only accept one more row. (This happens in
cases where you archive data.)
(3) You need to compact the data so that you can fit more rows in the table
by re-assigning keys to smaller values.
(4) The only solution in this case is to do what Keith is asking..
This occurs often in apps that use identity, acquire lots of data and
perform regular archiving, leaving massive gaps in the ID ranges. It's also
not only common on SQL Server, it happens on all major DBMS.
Regards,
Greg Linwood
SQL Server MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> this implies that you are placing meaning on the numbers.
> PDX, Oregon|||Hi Dick.
Yes - proliferating the keys down to any referencing foreign keys is a
requirement, but this is not susceptible to errors as long as it's planned
well.
If there is any maintenance window, this can be performed as part of regular
maintenance. If not, it needs to be performed transactionally and scheduled
regularly enough that this is not a large burden on the system.
Regards,
Greg Linwood
SQL Server MVP
"Dick" <deacdb2@.hotmail.com> wrote in message
news:8F1D7D0A-5669-4A9E-8434-EA2101D3CE9F@.microsoft.com...
> Greg,
> Good points.
> But then you have to proliferate the new PK values to all child table
foreign keys that refer to this table, yes? Sounds like an operation very
susceptible to errors.
> Thanks,
> Dick
>
> -- Greg Linwood wrote: --
> It doesn't necessarily imply that he's placing meaning on the
numbers. He
> might not even have designed the database!
> Compacting ID columns can be an important maintenance task,
particularly to
> avoid overloading ID columns that use smaller int types, such as
smallint.
> Simple example:
> (1) You've got a table that uses smallint for the identity datatype.
(3rd
> party app & you can't change this)
> (2) You have only 1 row, but they identity value is 32766, so you're
running
> out of space - the ID column will only accept one more row. (This
happens in
> cases where you archive data.)
> (3) You need to compact the data so that you can fit more rows in the
table
> by re-assigning keys to smaller values.
> (4) The only solution in this case is to do what Keith is asking..
> This occurs often in apps that use identity, acquire lots of data and
> perform regular archiving, leaving massive gaps in the ID ranges.
It's also[vbcol=seagreen]
> not only common on SQL Server, it happens on all major DBMS.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
> news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...

Renumbering an Identity Column

Is there any way to renumber an identity column?
Due to rows being deleted etc., I have identity columns that go 1, 3, 22, 23
etc.
Is there a way to renumber them 1, 2, 3, 4 etc.?Hi,
1. Take the data out to a temp table
2. Truncate the original table or use DBCC CHECKIDENT with reseed option to
reset the value back to 1
3. Load the data back to the table. Please do not include the identity
column while insertion. This will generate the
values in order .
Thanks
Hari
MCDBA
"Keith" <@..> wrote in message news:uUj9uRSKEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Is there any way to renumber an identity column?
> Due to rows being deleted etc., I have identity columns that go 1, 3, 22,
23
> etc.
> Is there a way to renumber them 1, 2, 3, 4 etc.?
>|||"Keith" <@..> wrote in message news:uUj9uRSKEHA.1348@.TK2MSFTNGP12.phx.gbl...
> Is there any way to renumber an identity column?
> Due to rows being deleted etc., I have identity columns that go 1, 3, 22,
23
> etc.
> Is there a way to renumber them 1, 2, 3, 4 etc.?
As Hari showed, yes there is a way.
Remember this violates the rules of database integrity|||this implies that you are placing meaning on the numbers.
this is bad design.
(For what it's worth)
Greg Jackson
PDX, Oregon|||It doesn't necessarily imply that he's placing meaning on the numbers. He
might not even have designed the database!
Compacting ID columns can be an important maintenance task, particularly to
avoid overloading ID columns that use smaller int types, such as smallint.
Simple example:
(1) You've got a table that uses smallint for the identity datatype. (3rd
party app & you can't change this)
(2) You have only 1 row, but they identity value is 32766, so you're running
out of space - the ID column will only accept one more row. (This happens in
cases where you archive data.)
(3) You need to compact the data so that you can fit more rows in the table
by re-assigning keys to smaller values.
(4) The only solution in this case is to do what Keith is asking..
This occurs often in apps that use identity, acquire lots of data and
perform regular archiving, leaving massive gaps in the ID ranges. It's also
not only common on SQL Server, it happens on all major DBMS.
Regards,
Greg Linwood
SQL Server MVP
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...
> this implies that you are placing meaning on the numbers.
> this is bad design.
> (For what it's worth)
>
> Greg Jackson
> PDX, Oregon
>|||Greg
Good points.
But then you have to proliferate the new PK values to all child table foreign keys that refer to this table, yes? Sounds like an operation very susceptible to errors
Thanks
Dic
-- Greg Linwood wrote: --
It doesn't necessarily imply that he's placing meaning on the numbers. H
might not even have designed the database
Compacting ID columns can be an important maintenance task, particularly t
avoid overloading ID columns that use smaller int types, such as smallint
Simple example
(1) You've got a table that uses smallint for the identity datatype. (3r
party app & you can't change this
(2) You have only 1 row, but they identity value is 32766, so you're runnin
out of space - the ID column will only accept one more row. (This happens i
cases where you archive data.
(3) You need to compact the data so that you can fit more rows in the tabl
by re-assigning keys to smaller values
(4) The only solution in this case is to do what Keith is asking.
This occurs often in apps that use identity, acquire lots of data an
perform regular archiving, leaving massive gaps in the ID ranges. It's als
not only common on SQL Server, it happens on all major DBMS
Regards
Greg Linwoo
SQL Server MV
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in messag
news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl..
> this implies that you are placing meaning on the numbers
>> this is bad design
>> (For what it's worth
>> Greg Jackso
> PDX, Orego
>>|||Hi Dick.
Yes - proliferating the keys down to any referencing foreign keys is a
requirement, but this is not susceptible to errors as long as it's planned
well.
If there is any maintenance window, this can be performed as part of regular
maintenance. If not, it needs to be performed transactionally and scheduled
regularly enough that this is not a large burden on the system.
Regards,
Greg Linwood
SQL Server MVP
"Dick" <deacdb2@.hotmail.com> wrote in message
news:8F1D7D0A-5669-4A9E-8434-EA2101D3CE9F@.microsoft.com...
> Greg,
> Good points.
> But then you have to proliferate the new PK values to all child table
foreign keys that refer to this table, yes? Sounds like an operation very
susceptible to errors.
> Thanks,
> Dick
>
> -- Greg Linwood wrote: --
> It doesn't necessarily imply that he's placing meaning on the
numbers. He
> might not even have designed the database!
> Compacting ID columns can be an important maintenance task,
particularly to
> avoid overloading ID columns that use smaller int types, such as
smallint.
> Simple example:
> (1) You've got a table that uses smallint for the identity datatype.
(3rd
> party app & you can't change this)
> (2) You have only 1 row, but they identity value is 32766, so you're
running
> out of space - the ID column will only accept one more row. (This
happens in
> cases where you archive data.)
> (3) You need to compact the data so that you can fit more rows in the
table
> by re-assigning keys to smaller values.
> (4) The only solution in this case is to do what Keith is asking..
> This occurs often in apps that use identity, acquire lots of data and
> perform regular archiving, leaving massive gaps in the ID ranges.
It's also
> not only common on SQL Server, it happens on all major DBMS.
> Regards,
> Greg Linwood
> SQL Server MVP
> "Jaxon" <GregoryAJackson@.hotmail.com> wrote in message
> news:uNve1pUKEHA.3016@.tk2msftngp13.phx.gbl...
> > this implies that you are placing meaning on the numbers.
> >> this is bad design.
> >> (For what it's worth)
> >> Greg Jackson
> > PDX, Oregon
> >>