2

I used below SQL to create a table with auto_increment primary key,but found after truncate the table the primary key is not reset to zero ,because after insert data to it the primary key continues to increase from the last time truncate. I believe the primary will be too large to cause the overflow. how to solve it?

CREATE TABLE dbo.BM_SM_ERR
(
    SMCWBM int          identity,   -- primary key
    SMCWDM varchar(10)  NOT NULL,   
    PRIMARY KEY CLUSTERED (SMCWBM)
)
with identity_gap=1

sybase version Adaptive Server Enterprise 15.7

Tom
  • 121
  • 2
  • 14

1 Answers1

3

The identity does not reset after a delete, truncate table or shutdown. You have to reset it manually if you want it with the sp_chgattribute procedure:

1> insert into BM_SM_ERR(SMCWDM) values ('x')
2> go
(1 row affected)
1> insert into BM_SM_ERR(SMCWDM) values ('y')
2> go
(1 row affected)
1> insert into BM_SM_ERR(SMCWDM) values ('z')
2> go
(1 row affected)
1> select * from BM_SM_ERR
2> go
 SMCWBM      SMCWDM     
 ----------- ---------- 
           1 x          
           2 y          
           3 z          

(3 rows affected)
1> truncate table BM_SM_ERR
2> go
1> insert into BM_SM_ERR(SMCWDM) values ('v')
2> go
(1 row affected)
1> select * from BM_SM_ERR
2> go
 SMCWBM      SMCWDM     
 ----------- ---------- 
           4 v          

(1 row affected)
1> truncate table BM_SM_ERR
2> go
1> exec sp_chgattribute BM_SM_ERR, 'identity_burn_max', 0, '0'
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
'identity_burn_max' attribute of object 'BM_SM_ERR' changed to 0.
(return status = 0)
1> insert into BM_SM_ERR(SMCWDM) values ('q')
2> go
(1 row affected)
1> select * from BM_SM_ERR
2> go
 SMCWBM      SMCWDM     
 ----------- ---------- 
           1 q          

(1 row affected)
Adam Leszczyński
  • 1,079
  • 7
  • 13