Resetting Identity column

[Yashpal K. Verma]

Hi,

I am creating a table with identity column. When I am deleting all the
records from the table, I want to reset the identity column to start from 1
again.

For example, I have a table IDTest with two columns
-> NameID - This is my identity column
-> Name - This is data column.
I inserted 10 records.
I deleted all. If I insert new record now then it will have 11 as NameID
value. I want to reset this to start from 1 again.

I am looking for the similer function as available in MS SQL Server - (DBCC
checkident (tablename, reseeed, 0)).

TIA

-Yash-

[m***@peppler.org]

Post by Yashpal K. Verma
Hi,
I am creating a table with identity column. When I am
deleting all the records from the table, I want to reset
the identity column to start from 1 again.
For example, I have a table IDTest with two columns
-> NameID - This is my identity column
-> Name - This is data column.
I inserted 10 records.
I deleted all. If I insert new record now then it will
have 11 as NameID value. I want to reset this to start
from 1 again.

I think you can use "set identity_insert" and/or "set
identity_update" for this.

But you should look at http://www.sypron.nl/idgaps.html for
the full details on various tricks to manage identity
columns with Sybase.

Michael

[Yashpal K. Verma]

Post by m***@peppler.org

Post by Yashpal K. Verma
Hi,
I am creating a table with identity column. When I am
deleting all the records from the table, I want to reset
the identity column to start from 1 again.
For example, I have a table IDTest with two columns
-> NameID - This is my identity column
-> Name - This is data column.
I inserted 10 records.
I deleted all. If I insert new record now then it will
have 11 as NameID value. I want to reset this to start
from 1 again.

I think you can use "set identity_insert" and/or "set
identity_update" for this.
But you should look at http://www.sypron.nl/idgaps.html for
the full details on various tricks to manage identity
columns with Sybase.
Michael

I am not looking for filling the Identity Gap but I am looking for resetting
the counter of identity column (without recreating my table).
I can use SET IDENTITY_INSERT for forcing my id column values but that is
not what I am looking for.
Any help is appreciated.
-Yash-

[m***@peppler.org]

Post by Yashpal K. Verma

Post by m***@peppler.org
But you should look at http://www.sypron.nl/idgaps.html
for the full details on various tricks to manage
identity columns with Sybase.
Michael

I am not looking for filling the Identity Gap but I am
looking for resetting the counter of identity column
(without recreating my table).

Last time I looked Rob's page detailed how to reset the next
identity value for the column to any value - and should
therefore should satisfy your request.

Michael

[Jesus M. Salvo Jr.]

Adaptive Server Enterprise/12.5.3/EBF 12330 ESD#1/P/Sun_svr4/OS
5.8/ase1253/1900/32-bit/FBO/Tue Jan 25 07:02:08 2005
I have "enable housekeeper GC" set to 4.

I reset "enable housekeeper GC" to 1 ... and HK GC stopped doing that having
read physical I/O on the index. It was running since on that same index
since at least yesterday!

[Jesus M. Salvo Jr.]

Post by Jesus M. Salvo Jr.

Adaptive Server Enterprise/12.5.3/EBF 12330 ESD#1/P/Sun_svr4/OS
5.8/ase1253/1900/32-bit/FBO/Tue Jan 25 07:02:08 2005
I have "enable housekeeper GC" set to 4.

I reset "enable housekeeper GC" to 1 ... and HK GC stopped doing that
having read physical I/O on the index. It was running since on that same
index since at least yesterday!

Resetting "enable housekeeper GC" to either 4 or 5 exhibits the behaviour
that I was seeing.

[Sherlock, Kevin]

Take a look at:

exec sp_chgattribute <table name> , 'identity_burn_max',0,'<next id setting>'

Post by Yashpal K. Verma
Hi,
I am creating a table with identity column. When I am deleting all the
records from the table, I want to reset the identity column to start from 1
again.
For example, I have a table IDTest with two columns
-> NameID - This is my identity column
-> Name - This is data column.
I inserted 10 records.
I deleted all. If I insert new record now then it will have 11 as NameID
value. I want to reset this to start from 1 again.
I am looking for the similer function as available in MS SQL Server - (DBCC
checkident (tablename, reseeed, 0)).
TIA
-Yash-

[Yashpal K. Verma]

Hi Sherlock,

This is more exact to my need. Thankyou :-)

I urther drill down the sp_chgattribute and found that it does a lot of
error checking and following line is doing the real stuff -

dbcc set_identity_burn_max(@dbname, @objname, @optvalue2)
so in my case it's -
dbcc set_identty_burn_max('MyDB', 'IDTest', '1')

The lowest value accpted by this is 1. The record I am inserting starts with
2 in ID field. Though it's lot better solution but it is still not resetting
it from zero. Looks like that for first row, I need to force id=1 (using set
identity_insert IDTest on/off).

Thanx for your help.

-Yash-

Post by Sherlock, Kevin
exec sp_chgattribute <table name> , 'identity_burn_max',0,'<next id setting>'

Post by Yashpal K. Verma
Hi,
I am creating a table with identity column. When I am deleting all the
records from the table, I want to reset the identity column to start from 1
again.
For example, I have a table IDTest with two columns
-> NameID - This is my identity column
-> Name - This is data column.
I inserted 10 records.
I deleted all. If I insert new record now then it will have 11 as NameID
value. I want to reset this to start from 1 again.
I am looking for the similer function as available in MS SQL Server - (DBCC
checkident (tablename, reseeed, 0)).
TIA
-Yash-