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 发表评论