Skip to Content
author's profile photo Former Member
Former Member

SYBASE ASE Identity column

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>

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Posted on Jul 28, 2016 at 12:34 PM

    "length" is the number of bytes the datatype uses.

    999,999,999 decimal is 3b9ac9ff in hex (4 bytes), the NUMERIC datatype uses an additional byte for overhead including the sign bit, for a total of 5 bytes.

    -bret

    Add a comment
    10|10000 characters needed characters exceeded

    • If you think you'll run out of identity values at some point, thus requiring an 'alter table' command, I'd suggest you give some serious thought to your current max identity value and set it now (eg, instead of numeric(9) use something like numeric(12)).

      To minimize the chance of running out of identity values you'll want to make sure you have a relatively small gap introduced if/when the dataserver is shutdown hard. (see sp_chgattribute/identity_gap)

      While the correct method of increasing your identity column size is to use the 'alter table' command, keep in mind that this will require making a copy of the current table. So, assuming you have something like 900 million rows in your table when you decide to increase your identity column size, keep in mind the time and space requirements for the 'alter table' command to make a copy of a 900 million row table. And don't forget the time to run 'alter table' against any other tables that happen to have a foreign key constraint back to your identity column. (Hence the suggestion you give some thought to defining your max identity value now ... so you don't have to alter the table in the future).

      Do a google search for plenty of posts regarding identity columns, identity gaps, changing identity column sizes, eg:

      Use of shutdown with nowait can lead to gaps in... | SCN

      Efficient way to alter a large table with limit... | SCN

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.