Sybase Testing Ref - Sybase Identity

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

BeanSoft 2007-02-06 17:56 发表评论