|
Home > Archive > ASE Database forum > October 2005 > Resetting Identity column
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Resetting Identity column
|
|
| Yashpal K. Verma 2005-10-27, 8:21 am |
| 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-
| |
| mpeppler@peppler.org 2005-10-27, 8:21 am |
| > 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 2005-10-27, 8:21 am |
|
<mpeppler@peppler.org> wrote in message
news:4354b79c.34d3.1681692777@sybase.com...
>
> 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-
| |
| mpeppler@peppler.org 2005-10-27, 8:21 am |
| > <mpeppler@peppler.org> wrote in message
> news:4354b79c.34d3.1681692777@sybase.com...
>
> 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
| |
| Sherlock, Kevin 2005-10-27, 8:21 am |
| Take a look at:
exec sp_chgattribute <table name> , 'identity_burn_max',
0,'<next id setting>'
"Yashpal K. Verma" <yashpalkv@gmail.com> wrote in message
news:43548072@forums
-2-dub...
> 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 2005-10-27, 8:21 am |
| 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_ma
x(@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-
"Sherlock, Kevin" <ksherlock@saionline.com> wrote in message
news:43553c76$1@foru
ms-1-dub...
> Take a look at:
>
> exec sp_chgattribute <table name> , 'identity_burn_max',
0,'<next id
> setting>'
>
>
> "Yashpal K. Verma" <yashpalkv@gmail.com> wrote in message
> news:43548072@forums
-2-dub...
>
>
|
|
|
|
|