Skip to Content
0
Former Member
Jul 28, 2016 at 11:33 AM

SYBASE ASE Identity column

1773 Views

Identity column in SYBASE .

We have identity column in production database tables and identity precision value is set to 9 . ( So I believe , identity value assignment by ASE can go till 999999999 ( 9 times 9 ) )

Can anyone explain about "Length" from the sp_help output ( What it means/says )

From below outputs while i am declaring precision as 9 for identity column it says length is "5" and for precision value "1" it says length value as 2 from "sp_help " output

Thanks in advance

1> create table john4 (john_id numeric(9,0) identity,

2> name char(10) not null)

3> go

1> sp_help john4

2> go

Name Owner Object_type Create_date

----- ----- ----------- -------------------

john4 dbo user table Jul 28 2016 7:14PM

(1 row affected)

Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity

----------- ------- ------ ---- ----- ----- ------------ --------- ---------------- ---------------------- ----------

john_id numeric 5 9 0 0 NULL NULL NULL NULL 1

name char 10 NULL NULL 0 NULL NULL NULL NULL 0

Object does not have any indexes.

No defined keys for this object.

name type partition_type partitions partition_keys

----- ---------- -------------- ---------- --------------

john4 base table roundrobin 1 NULL

partition_name partition_id pages row_count segment create_date

--------------- ------------ ----- --------- ------- -------------------

john4_960003420 960003420 1 0 default Jul 28 2016 7:14PM

Partition_Conditions

--------------------

NULL

Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/Avg)

----------- ----------- ----------- --------------------------- ---------------------------

1 1 1 1.000000 1.000000

Lock scheme Datarows

The 'ascinserts' attribute is not applicable to tables with datarow or datapage lock schemes.

exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ascinserts

------------ -------------- ---------- ----------------- ------------ -----------

1 0 0 0 0 0

(1 row affected)

concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg

------------------------- --------------------- -------------------

15 0 0

(return status = 0)

1> create table john5 (john_id numeric(1,0) identity,

2> name char(10) not null)

3> go

1> sp_help john5

2> go

Name Owner Object_type Create_date

----- ----- ----------- -------------------

john5 dbo user table Jul 28 2016 7:15PM

(1 row affected)

Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity

----------- ------- ------ ---- ----- ----- ------------ --------- ---------------- ---------------------- ----------

john_id numeric 2 1 0 0 NULL NULL NULL NULL 1

name char 10 NULL NULL 0 NULL NULL NULL NULL 0

Object does not have any indexes.

No defined keys for this object.

name type partition_type partitions partition_keys

----- ---------- -------------- ---------- --------------

john5 base table roundrobin 1 NULL

partition_name partition_id pages row_count segment create_date

--------------- ------------ ----- --------- ------- -------------------

john5_992003534 992003534 1 0 default Jul 28 2016 7:15PM

Partition_Conditions

--------------------

NULL

Avg_pages Max_pages Min_pages Ratio(Max/Avg) Ratio(Min/Avg)

----------- ----------- ----------- --------------------------- ---------------------------

1 1 1 1.000000 1.000000

Lock scheme Datarows

The 'ascinserts' attribute is not applicable to tables with datarow or datapage lock schemes.

exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap ascinserts

------------ -------------- ---------- ----------------- ------------ -----------

1 0 0 0 0 0

(1 row affected)

concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg

------------------------- --------------------- -------------------

15 0 0

(return status = 0)

1>