Sybase Identity
***********
June 2006
About the tutorial
n To introduce some basic concepts of IDENTITY
n To introduce how to use IDENTITY properly
Basic Concept
n What's IDENTITY
- Auto increment column
n Advantage of IDENTITY
- Auto generated, high efficiency
n Using IDENTITY
- Number leaking
Basic Concept
n DDL in creating TABLE
- ColumnName numeric(N) identity
- with identity_gap = M (optional)
- Default gap is 0, using server setting
- Data type can be INTEGER, and numeric(N), N [1,31]
Basic Concept
n Sample DDL
CREATE TABLE order_main(
order_no NUMERIC(20) IDENTITY
,cust_no NUMERIC(20) NOT NULL
,comments VARCHAR(256) NULL
)
LOCK DATAROWS
WITH IDENTITY_GAP=100
Basic Concept
n Identity Gap
Basic Concept
n Sp_help TableName
- Show which field is identity
- Show the value of identity_gap
- sample
Other Issues
n Only one identity column in a table
n Identity value cannot be reset
n Initial value is always 1
n Manually insert on identity column
- Set identity_insert Table on/off
- session scope
- Only one table with identity_insert on in a session
Other Issues
n @@identity -session scope
n reach maximum value
n Modify identity_gap
- sp_chgattribute "TableName","identity_gap",20
n Modify Global identity_gap
- sp_configure
"identity burning set factor"5000
Other Issues
n Sample DDL
Find table with IDENTITY column:
SELECT name FROM sysobjects WHERE next_identity(name) IS NOT NULL AND type='U'
Change gap:
sp_chgattribute 'cmdm_sign_log','identity_gap',1000
The End
THANKS
![98399.html](https://i-blog.csdnimg.cn/blog_migrate/29609b573413271ea447cfb084678e8d.jpeg)