What Datatypes Support AutoIncrement - which is best?

[glenn_barber]

We have an application developed in powerbuilder which has survived from the very early versions of SA. We are now on V11 and planning to move to V12.

Historically we had been using a double as the datatype for our autoincrement. We ar enow finding that there are issues with the double when attempting to create immediate refresh materialized views.

What are other good choices for autoincrement datatypes that will work with immediate refresh views? We will need to make sure these types are compatible with datawindow behavior (wherein the ID is refreshed in the ID column in the datawindow after an Insert)

[MCMartin]

I am just curious, what is your problem with doubles and materialized views?

[glenn_barber]

When attempting to create an Immediate Refresh material view SA11 gives a message something like "approximate datatypes are not supported". It does not flag it as a problem when the material view is not immediate.

[Breck_Carter]

IMO you should stick to BIGINT since that agrees with the data type for ISYSTABCOL.max_identity, the column that records the current largest value. IMO smaller integer types would be OK if space saving was important, but UNSIGNED BIGINT is asking for trouble, and fractional types are just not appropriate.

CREATE TABLE SYS.ISYSTABCOL ( 
   table_id        /* PK FK     */ UNSIGNED INT NOT NULL,
   column_id       /* PK        */ UNSIGNED INT NOT NULL,
   domain_id       /*    FK     */ SMALLINT NOT NULL,
   nulls                           CHAR ( 1 ) NOT NULL,
   width                           UNSIGNED INT NOT NULL,
   scale                           SMALLINT NOT NULL,
   object_id       /*    FK     */ UNSIGNED BIGINT NOT NULL,
   max_identity                    BIGINT NOT NULL,
   column_name                     CHAR ( 128 ) NOT NULL,
   "default"                       LONG VARCHAR NULL,
   user_type       /*    FK     */ SMALLINT NULL,
   column_type                     CHAR ( 1 ) NOT NULL,
   "compressed"                    TINYINT NOT NULL,
   collect_stats                   TINYINT NOT NULL,
   inline_max                      SMALLINT NULL,
   inline_long                     SMALLINT NULL,
   lob_index                       TINYINT NULL,
   CONSTRAINT PRIMARY KEY ( -- 16k
      table_id,
      column_id )
 );

[glenn_barber]

I noticed that my PowerDesigner 15 Physical Architect will not create a script to modify the existing data from a double to a big int. If I wish to preserve the values (which should be all integer values) , do I need to edit the inserts (commented with a warning) to do a cast of some form?

[reimer_pods]

If you do something like
ALTER TABLE "dba"."Test" ALTER "doubleval" BIGINT
the database will convert the existing values to the new type, if this is possible. In my testcase, the double values were successfully converted.

As Breck always says: Watcom (Sybase, iAnywhere ...) does things the way they should be done.

[VolkerBarth]

I'm somewhat surprised that a DOUBLE with DEFAULT AUTOINCREMENT does work...as to the docs:

Autoincrement is intended to work with positive integers.

However, such an approximate datatype might be error-prone w.r.t. rounding, as soon as the values are involved in arithmetics. (If you just read and write the values, that might be not problematic.)

In addition to classic SMALLINT, INT and BIGINT types, I would expect NUMERIC(n, 0) (and DECIMAL(n, 0)) to work as well - they just require an integral value. Personally, I would always prefer the (BIG) INT types for performance reasons.

---

I can't tell for Powerbuilder but all AUTOINCREMENT columns can be set automatically (and you can use SELECT @@IDENTITY to get the value afterwards), or can get pre-calculated values with SELECT get_identity(). - As of v12, SEQUENCES might be another alternative.

[Former Member]

Depends on what type of db you are using, read the manual and look for the largest capacity integer datatype, and go for it. normally the largest capacity integer datatype is BIGINT UNSIGNED. You will be able to represent integers from 0 up to 18446744073709551615. But then you should keep in mind, 18446744073709551615 is the largest integer accepted. Even if you assign length 50, it will still accept 20 digits only.

[VolkerBarth]

I would not generally recommend BIGINT if you don't expect millions/billions of rows in your particular tables. If a smaller type does fit, I would go for it. (Though I would surely agree that's easier to use one AUTOINCREMENT type in a single database than several ones for different table sizes...)

[reimer_pods]

I agree with Volker. In many cases INTEGER is sufficient, UNSIGNED INTEGER will go up to 4,294,967,295 - that means a a good many rows. Of course there are applications where that number isn't enough, that's where BIGINT comes into play.

[Former Member]

Yes guys, you all are right. In most of the cases INTEGER is enough to use. That is up to the developer himself. He must be knowing which is sufficient for his db, how many rows he might have etc. I suggested BIGINT because the user said they had been using a 'double', so I figured out they must have a db where the number of rows can be up to 15 digits.

[glenn_barber]

The reason for the double is lost somewhere in the dim early beginnings of powerbuilder and its support for auto increment datatypes . There is still the question whether powerbuilder V11 will support the bigint as a auto increment datatype. Note that the powerbuilder datawindow automatically updates the applied auto increment value into the data window after an insert- so we have to make sure whatever datatype we convert to is supported by that behavior. For our purposes unsigned integer would be sufficient and probably the most likely candidate.

[former_SQLA_member1694880]

AFAIK all numeric types can be used as Autoincrement. However, DOUBLE, being an approximate data type, is the least logical option. My choice for Autoinc columns is INTEGER, although NUMERIC(n) works nicely, too.