Using an identity column is a handy way to have IDs generated automatically - it is widely thought that this
type of ID was difficult to control (gaps in IDs for example) .. indeed you can control the sequence via
the sp_chgattribute procedure. See below for a complete example.
3> create table zz_test (
4> rec_id integer identity not null,
5> rec_desc varchar(20) not null
6> )
1>
2> insert into zz_test (rec_desc) values ('yellow')
(1 row affected)
1> insert into zz_test (rec_desc) values ('orange')
(1 row affected)
1> insert into zz_test (rec_desc) values ('green')
(1 row affected)
1>
2>
3> select * from zz_test
rec_id rec_desc
----------- --------------------
1 yellow
2 orange
3 green
(3 rows affected)
1>
2>
3> truncate table zz_test
1>
2> sp_chgattribute zz_test, "identity_burn_max", 0, "1"
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
'identity_burn_max' attribute of object 'zz_test' changed to 1.
(return status = 0)
1>
2> insert into zz_test (rec_desc) values ('blue')
(1 row affected)
1> insert into zz_test (rec_desc) values ('red')
(1 row affected)
1> insert into zz_test (rec_desc) values ('pink')
(1 row affected)
1>
2>
3> select * from zz_test
rec_id rec_desc
----------- --------------------
2 blue
3 red
4 pink
(3 rows affected)
|
|
