locked
Size of autonumber RRS feed

  • Question

  • I was trying to find out the size of Autonumber field.

    In MS site it said "integer or long integer" - which one is it ?

    Thanks!

     

    Thursday, December 2, 2010 7:01 AM

Answers

  • Hi,

    it's absolutely long integer. Integer has an 'upper bound' = 32767, it's obviously too little.

    Moreover, look at this article http://support.microsoft.com/kb/209599

    Quote:

    values in an AutoNumber field increment from 1 to 2,147,483,647. The next record receives the minimum negative value for a Long Integer, -2,147,483,648. The AutoNumber value for each subsequent record is one greater than the previous value, until it reaches zero. Therefore, the maximum number of records that a table may hold if the primary key data type is set to AutoNumber is 4,294,967,295.

    And here is some info from MS Access built-in help:

    Integer Stores numbers from –32,768 to 32,767 (no fractions). None 2 bytes
    Long Integer (Default) Stores numbers from –2,147,483,648 to 2,147,483,647 (no fractions). None 4 bytes


    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by poppe2 Friday, December 3, 2010 6:27 AM
    Thursday, December 2, 2010 8:07 AM

All replies

  • Hi,

    it's absolutely long integer. Integer has an 'upper bound' = 32767, it's obviously too little.

    Moreover, look at this article http://support.microsoft.com/kb/209599

    Quote:

    values in an AutoNumber field increment from 1 to 2,147,483,647. The next record receives the minimum negative value for a Long Integer, -2,147,483,648. The AutoNumber value for each subsequent record is one greater than the previous value, until it reaches zero. Therefore, the maximum number of records that a table may hold if the primary key data type is set to AutoNumber is 4,294,967,295.

    And here is some info from MS Access built-in help:

    Integer Stores numbers from –32,768 to 32,767 (no fractions). None 2 bytes
    Long Integer (Default) Stores numbers from –2,147,483,648 to 2,147,483,647 (no fractions). None 4 bytes


    Andrey V Artemyev | Saint-Petersburg, Russia
    • Marked as answer by poppe2 Friday, December 3, 2010 6:27 AM
    Thursday, December 2, 2010 8:07 AM
  • Thanks
    Friday, December 3, 2010 6:27 AM
  • While the datatype of an Autonumber fields is Number with a Field Size of Long Integer it is important to note the identifier of Long Integer is used when the field properties are visualized through the Access User Interface.

    You see, if you're scope only encompasses the Jet/ACE database engine, then the verbage used to identify an Autonumber / Long Integer field changes to AUTOINCREMENT (or COUNTER) and INTEGER respectively. As indicated by the article found here:

    http://msdn.microsoft.com/en-us/library/bb208866(office.12).aspx

    It is important to know that both identifiers (Access's Number/Long Integer and Jet/ACE's INTEGER) both refer to a 4 byte signed integer. 

    So recognizing that the scope of the question is important, here is a list of the ways a 4 byte signed integer is identified [Scope : Identifier]

    Access UI : Datatype of Number with a Field Size of Long Integer

    Jet/ACE : INTEGER

    VBA: Long

    SQL Server : int

    C# : int

    ------

    Hope that helps clear up why you have seen an Autonumber field identified as both an Integer as Long Integer.


    Brent Spaulding | Access MVP
    Friday, December 3, 2010 7:03 AM
  • Brent,

    it's a strange trend do create many names for the same things. I used to be interested in Delphi (and Pascal earlier) where integer was always a 2 byte signed number w/o fractional part [-2^15;2^15). While reading your post I understand that I know all these things but I've never thought about them alltogether at one time. :) Anyway, thanks for explanation. Now it's absolutely doubtless clear.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Friday, December 3, 2010 7:24 AM