An identity can be thought of as a special form of default value that can be applied
to a column in a table. When applied to a numeric column, a new integer value
is inserted automatically for each new data row. However, unlike with default
constraints, a value cannot normally be specified to override the identity value.
In other database management systems an identity may be called an autonumber
or automatically incrementing value.
The identity column value is selected based upon the identity
specification, which consists of a seed and an increment value.
The first row inserted takes the value specified in the seed. Subsequent rows
use a number that is calculated by adding the increment value to the previous
value. Commonly the seed and increment values are both set to one to give a
natural sequence. It is quite acceptable, however, to start with a seed that is
negative, potentially doubling the number of available values that may be
generated.
An identity can only be added to a single column within any table.
Additionally, they can only be added to numeric columns that store integer
values. These include TinyInt, SmallInt, Int and BigInt columns. You can also
apply an identity to numeric or decimal columns if the scale for the data type
is set to zero.
Identity columns are often used as surrogate primary keys as they can be used
to generate unique values when used correctly. However, it is possible that an
identity can generate a duplicate value if existing rows are within the range
of an identity specification. Additionally, you should not rely on identities
to provide a sequential set of numbers. It is possible that identity values can
be skipped following an aborted or failed insert. Also, the sequence will be
broken, leaving holes in the range, if rows are deleted from the table.
Adding an Identity to a Column
There are several ways in which an identity can be applied to a
column in a table. In this article we will consider two options: adding
identities using the SQL Server Management Studio graphical user interface
tools and creating them using Transact-SQL statements.
In the article we will add identity specifications to columns in
the JoBS tutorial database. This database has been created over
the course of this tutorial. If you have not followed the tutorial, download
and run the scripts using the link at the top of this page to create the
database and sample data. You can also use this script if you wish to refresh
your copy of the database and add items from the previous article.
Adding an Identity Specification
Using SQL Server Management Studio
The easiest manner of defining an identity in a database is using
the table designer in SQL Server Management Studio (SSMS). An identity can be added
to a table when it is first created or at a later time, even if the table
already contains information.
The Contracts table in the Jobs database contains an integer-based
surrogate primary key that is an ideal candidate for using an identity column. To
create this identity, open the table designer for the table and select the
first row in the grid. This row represents the ContractNumber column.
Once the row is selected, the column properties will be updated to
show, and allow editing of, the column settings. Within the "Table
Designer" section you should see the "Identity Specification"
option. This option will initially be set to No. Using the + icon to the left
of the property, expand the grid to show the three available identity options:
§ (Is Identity). This property
determines whether the column uses an identity. When set to No, the two other
properties may not be modified.
§ Identity Increment. This value is added
to the previous identity value each time a new row is added to the table. The
resultant value is stored in the column. The value can be a positive or
negative value but may not be zero.
§ Identity Seed. This value is the
number that will be used in the identity column for the first row inserted into
the table.
In this case, we want the first contract number to be one and we
want this number to increase by one each time we insert a new row. To apply
these settings, set (Is Identity) to Yes and set both the seed and
increment values to 1. To retain the changes, save the table design and close
the designer window.
No comments:
Post a Comment