Chapter 9 SQL Statements
469
On inserts into the table, if a value is not specified for the
AUTOINCREMENT column, a unique value larger than any other
value in the column is generated. If an INSERT specifies a value for
the column, it is used; if the specified value is larger than the
current maximum value for the column, that value will be used as a
starting point for subsequent inserts.
Deleting rows does not decrement the AUTOINCREMENT
counter. Gaps created by deleting rows can only be filled by explicit
assignment when using an insert. After an explicit insert of a row
number less then the maximum, subsequent rows without explicit
assignment are still automatically incremented with a value of one
greater than the previous maximum.
The next value to be used for each column is stored as an integer.
Using values greater than (2**31 – 1) may cause wraparound to
incorrect values, and AUTOINCREMENT should not be used in
such cases.
You can find the most recently inserted value of the column by
inspecting the @@identity global variable.
The identity column is a Transact-SQL-compatible alternative to
using the AUTOINCREMENT default. In Adaptive Server
Anywhere, the identity column is implemented as
AUTOINCREMENT default. For information, see "The special
IDENTITY column" on page 973 of the book ASA User’s Guide.
♦
GLOBAL AUTOINCREMENT This default is intended for use
when multiple databases will be used in a replication environment.
This default is similar to AUTOINCREMENT, with the following
exceptions.
The domain is partitioned. The size of each partition can be
specified in parentheses immediately following the
AUTOINCREMENT key word. This value may be any positive
integer, although the partition size is generally chosen so that the
supply of numbers within any one partition will rarely, if ever, be
exhausted. If the column is of type BIGINT or UNSIGNED
BIGINT, the default partition size is 2**32 = 4294967296; for
columns of other types the default partition size is 2**16 = 65536.
To Next Page
Loading...