AutoNumber

Last updated

AutoNumber is a type of data used in Microsoft Access tables to generate an automatically incremented numeric counter. It may be used to create an identity column which uniquely identifies each record of a table. Only one AutoNumber is allowed in each table.

Contents

The data type was called Counter in Access 2.0. [1]

Forms

There are three forms in which AutoNumbers can be generated: [2]

start value plus increment
AutoNumbers generated by this mechanism start with the start number and increment with the increment value, checking for collision with existing table rows. [2]
random
AutoNumbers generated by this mechanism are assigned using a pseudo-random number generator that generates long integers and checks for collisions with existing table rows. [2]
replication IDs
AutoNumbers generated by this mechanism are IDs generated in a manner to make it highly improbable that collisions will occur. [2] They are Microsoft Globally Unique Identifiers, and the probability of collision is low until the year AD 3400. [3]

The default size of an AutoNumber is a 4-byte (long) integer. This is the size used for start+increment and random AutoNumbers. For replication ID AutoNumbers, the FieldSize property of the field is changed from long integer to Replication ID. [2]

If an AutoNumber is a long integer, the NewValues property determines whether it is of the start+increment or random form. The values that this property can take are "Increment" and "Random". [4]

Use

The default AutoNumber type is a start+increment counter, with a start value of 1 and an increment of 1. Although in many instances such an AutoNumber field will appear as if it contains the row count, it does not. Deletion of rows from a table, for example, does not cause AutoNumber fields to be re-numbered, but instead leaves "holes" in the numbering. Similarly, if a transaction to add a row to a table is begun but later aborted, the AutoNumber assigned for that row will not be re-used. [2]

The default start+increment form with the start value of 1 and increment of 1 is not suitable for all circumstances. There are reasons to choose each form, and trade-offs in doing so. [2]

The default start and increment values might reveal information about a table that it is desired not to reveal to people viewing individual table rows. For example, using an AutoNumber field for a customer ID might reveal information that it is desirable not to reveal to, say, customer number 6. This is one example of occasion where the start value of an AutoNumber field is raised, so that customer number 6 has, say, AutoNumber field value 10006. [2]

Using random values is desirable in cases where it would be unfortunate if it were possible to guess the next values assigned to new rows in the table. This usage is rare, however. [2]

A common problem with AutoNumber fields is encountered if tables are replicated. If multiple users are using multiple replicas of the table, then it is likely that they will end up assigning the same values to AutoNumber fields in new rows that they add, causing replication conflicts when the replicas are merged. [2]

This problem is addressed in two ways. First, it is possible to use Replication IDs for such AutoNumbers. [2] Such replication IDs, being GUIDs, will be unique across replicas, with a low probability of collision. [3] Second, when Access creates table replicas, it automatically changes AutoNumbers of the start+increment form to the random form. [4]

Manipulation of counters using DDL

The following Data Definition Language (DDL) query creates an AutoNumber field with a start value and an increment:

CREATETABLETable1(Field1COUNTER([beginning_number],[increment_number]),[...]);

This query resets the counter:

ALTERTABLETable1ALTERCOLUMNField1COUNTER(beginning_number,increment_number);

An alternative method of resetting the counter is to drop column re-add it (this has the side effect of renumbering existing rows in the table):

ALTERTABLETable1DROPCOLUMNField1;ALTERTABLETable1ADDField1COUNTER;

Related Research Articles

In computer science, an array is a data structure consisting of a collection of elements, of same memory size, each identified by at least one array index or key. An array is stored such that the position of each element can be computed from its index tuple by a mathematical formula. The simplest type of data structure is a linear array, also called one-dimensional array.

Applesoft BASIC is a dialect of Microsoft BASIC, developed by Marc McDonald and Ric Weiland, supplied with the Apple II series of computers. It supersedes Integer BASIC and is the BASIC in ROM in all Apple II series computers after the original Apple II model. It is also referred to as FP BASIC because of the Apple DOS command used to invoke it, instead of INT for Integer BASIC.

<span class="mw-page-title-main">Hash function</span> Mapping arbitrary data to fixed-size values

A hash function is any function that can be used to map data of arbitrary size to fixed-size values, though there are some hash functions that support variable length output. The values returned by a hash function are called hash values, hash codes, digests, or simply hashes. The values are usually used to index a fixed-size table called a hash table. Use of a hash function to index a hash table is called hashing or scatter storage addressing.

<span class="mw-page-title-main">Universally unique identifier</span> Label used for information in computer systems

A Universally Unique IDentifier (UUID) is a 128-bit label used for information in computer systems. The term Globally Unique IDentifier (GUID) is also used, mostly in Microsoft systems.

<span class="mw-page-title-main">Extract, transform, load</span> Procedure in computing

In computing, extract, transform, load (ETL) is a three-phase process where data is extracted, transformed and loaded into an output data container. The data can be collated from one or more sources and it can also be output to one or more destinations. ETL processing is typically executed using software applications but it can also be done manually by system operators. ETL software typically automates the entire process and can be run manually or on reoccurring schedules either as single jobs or aggregated into a batch of jobs.

A foreign key is a set of attributes in a table that refers to the primary key of another table. The foreign key links these two tables. Another way to put it: In the context of relational databases, a foreign key is a set of attributes subject to a certain kind of inclusion dependency constraints, specifically a constraint that the tuples consisting of the foreign key attributes in one relation, R, must also exist in some other relation, S, and furthermore that those attributes must also be a candidate key in S. In simpler words, a foreign key is a set of attributes that references a candidate key. For example, a table called TEAM may have an attribute, MEMBER_NAME, which is a foreign key referencing a candidate key, PERSON_NAME, in the PERSON table. Since MEMBER_NAME is a foreign key, any value existing as the name of a member in TEAM must also exist as a person's name in the PERSON table; in other words, every member of a TEAM is also a PERSON.

Commodore BASIC, also known as PET BASIC or CBM-BASIC, is the dialect of the BASIC programming language used in Commodore International's 8-bit home computer line, stretching from the PET (1977) to the Commodore 128 (1985).

In computer programming, a magic number is any of the following:

A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data, unlike a natural key.

An SQL INSERT statement adds one or more records to any single table in a relational database.

NIS+ is a directory service developed by Sun Microsystems to replace its older 'NIS'. It is designed to eliminate the need for duplication across many computers of configuration data such as user accounts, host names and addresses, printer information and NFS disk mounts on individual systems, instead using a central repository on a master server, simplifying system administration. NIS+ client software has been ported to other Unix and Unix-like platforms.

Extensible Storage Engine (ESE), also known as JET Blue, is an ISAM data storage technology from Microsoft. ESE is the core of Microsoft Exchange Server, Active Directory, and Windows Search. It's also used by a number of Windows components including Windows Update client and Help and Support Center. Its purpose is to allow applications to store and retrieve data via indexed and sequential access.

AutoPlay, a feature introduced in Windows 98, examines newly discovered removable media and devices and, based on content such as pictures, music or video files, launches an appropriate application to play or display the content. It is closely related to the AutoRun operating system feature. AutoPlay was created in order to simplify the use of peripheral devices – MP3 players, memory cards, USB storage devices and others – by automatically starting the software needed to access and view the content on these devices. AutoPlay can be enhanced by AutoPlay-compatible software and hardware. It can be configured by the user to associate favourite applications with AutoPlay events and actions.

The Access Database Engine is a database engine on which several Microsoft products have been built. The first version of Jet was developed in 1992, consisting of three modules which could be used to manipulate a database.

<span class="mw-page-title-main">GUID Partition Table</span> Computer disk partitioning standard

The GUID Partition Table (GPT) is a standard for the layout of partition tables of a physical computer storage device, such as a hard disk drive or solid-state drive, using universally unique identifiers, which are also known as globally unique identifiers (GUIDs). Forming a part of the Unified Extensible Firmware Interface (UEFI) standard, it is nevertheless also used for some BIOSs, because of the limitations of master boot record (MBR) partition tables, which use 32 bits for logical block addressing (LBA) of traditional 512-byte disk sectors.

Entity–attribute–value model (EAV) is a data model to encode, in a space-efficient manner, entities where the number of attributes that can be used to describe them is potentially vast, but the number that will actually apply to a given entity is relatively modest. Such entities correspond to the mathematical notion of a sparse matrix.

In relational database management systems, a unique key is a candidate key. All the candidate keys of a relation can uniquely identify the records of the relation, but only one of them is used as the primary key of the relation. The remaining candidate keys are called unique keys because they can uniquely identify a record in a relation. Unique keys can consist of multiple columns. Unique keys are also called alternate keys. Unique keys are an alternative to the primary key of the relation. In SQL, the unique keys have a UNIQUE constraint assigned to them in order to prevent duplicates. Alternate keys may be used like the primary key when doing a single-table select or when filtering in a where clause, but are not typically used to join multiple tables.

An identity column is a column in a database table that is made up of values generated by the database. This is much like an AutoNumber field in Microsoft Access or a sequence in Oracle. Because the concept is so important in database science, many RDBMS systems implement some type of generated key, although each has its own terminology. Today a popular technique for generating identity is to generate a random UUID.

Microsoft SQL Server is a proprietary relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network. Microsoft markets at least a dozen different editions of Microsoft SQL Server, aimed at different audiences and for workloads ranging from small single-machine applications to large Internet-facing applications with many concurrent users.

Hi/Lo is an algorithm and a key generation strategy used for generating unique keys for use in a database as a primary key. It uses a sequence-based hi-lo pattern to generate values. It can be used with domain-driven design (DDD). Hi/Lo is used in scenarios where an application needs its entities to have an identity prior to persistence. It is a value generation strategy. An alternative to Hi/Lo would be to generate keys as universally unique identifiers (UUID).

References

  1. ↑ Prague, Cary & Michael Irwin. Access 2002 Bible. New York: Wiley Publishing, Inc. p. 109.
  2. 1 2 3 4 5 6 7 8 9 10 11 Chris Grover; Matthew MacDonald & Emily A. Vander Veer (2007). Office 2007: The Missing Manual. O'Reilly. pp. 636–638. ISBN   9780596514228.
  3. 1 2 Microsoft (2006-01-09). "KBID 170117: How to use GUID fields in Access from Visual C++". Microsoft KnowledgeBase. Microsoft.
  4. 1 2 Microsoft (2009). "Microsoft Access Visual Basic reference: NewValues Property". MSDN . Microsoft. Retrieved 2009-07-05.

Further reading

This page is based on this Wikipedia article
Text is available under the CC BY-SA 4.0 license; additional terms may apply.
Images, videos and audio are available under their respective licenses.