How can I renumber the following Invoice Nos. through Query.
After sorting by date, the 1st invoice no. to be started with 2006050001
Current Records in table
Before Query
InvNo Date
2006050133 12-MAY-06
2006050134 12-MAY-06
2006050135 12-MAY-06
2006050136 15-MAY-06
2006050137 04-MAY-06
2006050138 03-MAY-06
2006050139 03-MAY-06
2006050140 03-MAY-06
2006050141 03-MAY-06
2006050142 03-MAY-06
2006050143 03-MAY-06
After Query
InvNo Date
2006050001 03-MAY-06
2006050002 03-MAY-06
2006050003 03-MAY-06
2006050004 03-MAY-06
2006050005 03-MAY-06
2006050006 03-MAY-06
2006050007 04-MAY-06
2006050008 12-MAY-06
2006050009 12-MAY-06
2006050010 12-MAY-06
2006050011 15-MAY-06
Any idea please ?
Best Regards,
LuqmanCreate a new table with
Select InvNo ,Date Into NewTable From OriginalTable Order By Date -- I hope
you have more field to use in order by since the date is repeating
Then goto newtable and add a new Identity Field as ID
Now you should have
New Table
ID InvNo Date
1 2006050138 03-MAY-06
2 2006050139 03-MAY-06
3 etc
4
5
Now
you can update your original table by joining with new table and and
formating your new InvNo using ID field.
hope this helps,
erdal
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:OjrLclVhGHA.4404@.TK2MSFTNGP05.phx.gbl...
> How can I renumber the following Invoice Nos. through Query.
> After sorting by date, the 1st invoice no. to be started with 2006050001
> Current Records in table
> Before Query
> InvNo Date
> 2006050133 12-MAY-06
> 2006050134 12-MAY-06
> 2006050135 12-MAY-06
> 2006050136 15-MAY-06
> 2006050137 04-MAY-06
> 2006050138 03-MAY-06
> 2006050139 03-MAY-06
> 2006050140 03-MAY-06
> 2006050141 03-MAY-06
> 2006050142 03-MAY-06
> 2006050143 03-MAY-06
> After Query
> InvNo Date
> 2006050001 03-MAY-06
> 2006050002 03-MAY-06
> 2006050003 03-MAY-06
> 2006050004 03-MAY-06
> 2006050005 03-MAY-06
> 2006050006 03-MAY-06
> 2006050007 04-MAY-06
> 2006050008 12-MAY-06
> 2006050009 12-MAY-06
> 2006050010 12-MAY-06
> 2006050011 15-MAY-06
> Any idea please ?
>
> Best Regards,
> Luqman
>
>|||create table invoice_sam(
InvNo varchar(25),
Date smalldatetime)
insert into invoice_sam values('2006050133','12-MAY-06')
insert into invoice_sam values('2006050134','12-MAY-06')
insert into invoice_sam values('2006050135','12-MAY-06')
insert into invoice_sam values('2006050136','15-MAY-06')
insert into invoice_sam values('2006050137','04-MAY-06')
insert into invoice_sam values('2006050138','03-MAY-06')
insert into invoice_sam values('2006050139','03-MAY-06')
insert into invoice_sam values('2006050140','03-MAY-06')
insert into invoice_sam values('2006050141','03-MAY-06')
insert into invoice_sam values('2006050142','03-MAY-06')
insert into invoice_sam values('2006050143','03-MAY-06')
select identity(int,1,1) as sno,
invno,
date
into #step1
from invoice_sam
order by date asc
select substring(invno,1,6) +
case when sno between 1 and 9 then '000' + convert(varchar(5),sno)
when sno between 10 and 99 then '00' + convert(varchar(5),sno)
when sno between 100 and 999 then '0' + convert(varchar(5),sno)
else convert(varchar(5),sno)
End as invno,
date
from #step1
Regards
Sudarshan Selvaraja
"Luqman" wrote:
> How can I renumber the following Invoice Nos. through Query.
> After sorting by date, the 1st invoice no. to be started with 2006050001
> Current Records in table
> Before Query
> InvNo Date
> 2006050133 12-MAY-06
> 2006050134 12-MAY-06
> 2006050135 12-MAY-06
> 2006050136 15-MAY-06
> 2006050137 04-MAY-06
> 2006050138 03-MAY-06
> 2006050139 03-MAY-06
> 2006050140 03-MAY-06
> 2006050141 03-MAY-06
> 2006050142 03-MAY-06
> 2006050143 03-MAY-06
> After Query
> InvNo Date
> 2006050001 03-MAY-06
> 2006050002 03-MAY-06
> 2006050003 03-MAY-06
> 2006050004 03-MAY-06
> 2006050005 03-MAY-06
> 2006050006 03-MAY-06
> 2006050007 04-MAY-06
> 2006050008 12-MAY-06
> 2006050009 12-MAY-06
> 2006050010 12-MAY-06
> 2006050011 15-MAY-06
> Any idea please ?
>
> Best Regards,
> Luqman
>
>
>|||I hope you want the number to start from 1 for each month ..
You can try this Query
UPDATE table1 SET InvNum =
LEFT(CONVERT(VARCHAR,InvDate,112),6) +
REPLACE
(
STR(
(SELECT
COUNT(*)
FROM
Table1 t2
WHERE
t2.Num1 <= Table1.Num1
AND YEAR(Table1.InvDate) = YEAR(t2.InvDate) AND MONTH(Table1.InvDate) =
MONTH(t2.InvDate)
)
,4),
' ','0')
- Sha Anand
"Luqman" wrote:
> How can I renumber the following Invoice Nos. through Query.
> After sorting by date, the 1st invoice no. to be started with 2006050001
> Current Records in table
> Before Query
> InvNo Date
> 2006050133 12-MAY-06
> 2006050134 12-MAY-06
> 2006050135 12-MAY-06
> 2006050136 15-MAY-06
> 2006050137 04-MAY-06
> 2006050138 03-MAY-06
> 2006050139 03-MAY-06
> 2006050140 03-MAY-06
> 2006050141 03-MAY-06
> 2006050142 03-MAY-06
> 2006050143 03-MAY-06
> After Query
> InvNo Date
> 2006050001 03-MAY-06
> 2006050002 03-MAY-06
> 2006050003 03-MAY-06
> 2006050004 03-MAY-06
> 2006050005 03-MAY-06
> 2006050006 03-MAY-06
> 2006050007 04-MAY-06
> 2006050008 12-MAY-06
> 2006050009 12-MAY-06
> 2006050010 12-MAY-06
> 2006050011 15-MAY-06
> Any idea please ?
>
> Best Regards,
> Luqman
>
>
>|||A bit simpler, (not requiring a temporary table)
select left(s1.InvNo,6) +
right('0000'+
cast((select count(*)
from invoice_sam s2
where s2.Date < s1.Date
or (s2.Date = s1.Date
and s2.InvNo <= s1.InvNo)) as varchar(4)),4) as
InvNo,
Date
from invoice_sam s1
order by 1
Showing posts with label renumbering. Show all posts
Showing posts with label renumbering. Show all posts
Saturday, February 25, 2012
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...
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...
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
> >>
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
> >>
Subscribe to:
Posts (Atom)