cancel
Showing results for 
Search instead for 
Did you mean: 

SYBASE ASE Identity column

Former Member
0 Kudos

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>

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member188958
Active Contributor
0 Kudos

"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

Former Member
0 Kudos

Thanks a lot for quick response and explanation. Now i am clear about it .

Could you please clarify on below :

What is the max precision limit for SYBASE 15.7 SP64 version .

Right now , Precision for our production database tables set to "9" . We will begin with below actions if it's nearing the max value (999,999,999)

1) BCP Out

2) Truncate and drop the tables

3) Create table and do BCP in ( Without -E option )   Do we have any alternate way ?..

And , Can we modify precision limit from 9 to it's Max value ( What is the max value for SP64 version ) .

If it can be modified , Please find our plan and advise on it

Enable the select into bulk copy db option ,

1) Drop the indexes associated for that table

2) alter the table ( set the value to Max )

3) Create the indexes

4) Update statistics + dbcc upgrade object

former_member89972
Active Contributor
0 Kudos

ASE 15.x

numeric (precison , scale) values can go from 10**-38 to 10**38 -1 

precison + scale must be <= 38

Can take up storage from 4  to 17 bytes.

Your application(s) may break if new expanded identity (e.g. more than 1 B) width is not catered for.

Typically numeric(9,0) has been used for identity column to cater for  one billion unique numbers.

Most of the time this suffices for a few years and then the issue of running out of number surfaces.

HTH

Avinash