Identity Column :
Identity columns are
commonly used as primary keys in database tables. These columns
automatically assign a value for each new row inserted. This is column
property of sql server. we can use this property
when CREATE TABLE OR ALTER TABLE transact
sql statements.
Syntax:
IDENTITY [ (seed , increment) ]
Arguments
seed
This value is integer value, This value specifies which value need to start(Starting value/Is
the value that is used for the very first row loaded into the table.)
Is the incremental value that is added to the identity value of the previous row that was loaded.
A. Using the IDENTITY property with CREATE TABLE
USE AdventureWorks2012;
IF OBJECT_ID ('dbo.new_employees', 'U') IS NOT NULL
DROP TABLE new_employees;
GO
CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
);
INSERT new_employees
(fname, minit, lname)
VALUES
('Karin', 'F', 'Josephs');
INSERT new_employees
(fname, minit, lname)
VALUES
('Pirkko', 'O', 'Koskitalo');
Exceptions
Examples
A. Resetting the current identity value, if it is needed
USE AdventureWorks2012;
GO
DBCC CHECKIDENT ('Person.AddressType');
GO
B. Reporting the current identity value
USE AdventureWorks2012;
GO
DBCC CHECKIDENT ('Person.AddressType', NORESEED);
GO
C. Forcing the current identity value to a new value
USE AdventureWorks2012;
GO
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);
GO
IDENTITY Basics
Is the incremental value that is added to the identity value of the previous row that was loaded.
A. Using the IDENTITY property with CREATE TABLE
USE AdventureWorks2012;
IF OBJECT_ID ('dbo.new_employees', 'U') IS NOT NULL
DROP TABLE new_employees;
GO
CREATE TABLE new_employees
(
id_num int IDENTITY(1,1),
fname varchar (20),
minit char(1),
lname varchar(30)
);
INSERT new_employees
(fname, minit, lname)
VALUES
('Karin', 'F', 'Josephs');
INSERT new_employees
(fname, minit, lname)
VALUES
('Pirkko', 'O', 'Koskitalo');
Exceptions
|
Condition
|
Reset methods
|
|
The current identity
value is larger than the maximum value in the table.
|
Execute DBCC CHECKIDENT (table_name, NORESEED) to
determine the current maximum value in the column, and then specify that
value as the new_reseed_value in a DBCC CHECKIDENT (table_name, RESEED,new_reseed_value) command.
-OR- Execute DBCC CHECKIDENT (table_name, RESEED,new_reseed_value) with new_reseed_value set to a very low value, and then run DBCC CHECKIDENT (table_name, RESEED) to correct the value. |
|
All rows are deleted from
the table.
|
Execute DBCC CHECKIDENT (table_name, RESEED,new_reseed_value) with new_reseed_value set to the desired starting value.
|
Examples
A. Resetting the current identity value, if it is needed
USE AdventureWorks2012;
GO
DBCC CHECKIDENT ('Person.AddressType');
GO
B. Reporting the current identity value
USE AdventureWorks2012;
GO
DBCC CHECKIDENT ('Person.AddressType', NORESEED);
GO
C. Forcing the current identity value to a new value
USE AdventureWorks2012;
GO
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);
GO
IDENTITY Basics
increment
You must specify both
the seed and increment or neither. If neither is specified, the default is
(1,1).
Examples:
The
following example creates a new table using the
IDENTITY property
for an automatically increment identification number.
Identity
columns can be used for generating key values. The identity property on a
column guarantees the following:
·
Each new value is generated based on the current seed &
increment.
·
Each new value for a particular transaction is different from
other concurrent transactions on the table.
The identity property on a column does not guarantee the
following:
·
Uniqueness of the value – Uniqueness must be enforced by using a PRIMARY
KEY or UNIQUE constraint or UNIQUE index.
·
Consecutive values within a
transaction – A
transaction inserting multiple rows is not guaranteed to get consecutive values
for the rows because other concurrent inserts might occur on the table. If
values must be consecutive then the transaction should use an exclusive lock on
the table or use the SERIALIZABLE isolation level.
·
Consecutive values after
server restart or other failures –SQL Server might cache identity values
for performance reasons and some of the assigned values can be lost during a
database failure or server restart. This can result in gaps in the identity
value upon insert. If gaps are not acceptable then the application should use
its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to
transactions that are never committed.
·
Reuse of values – For a given identity property with
specific seed/increment, the identity values are not reused by the engine. If a
particular insert statement fails or if the insert statement is rolled back
then the c
Only one identity
column can be created per table.
In memory-optimized
tables the seed and increment must be set to 1,1. Setting the seed or increment
to a value other than 1 results in the following error: The use of seed and
increment values other than 1 is not supported with memory optimized tables.
The user issuing the statement must own the
object, be a system administrator (sysadmin role), be the database owner (dbo)
or be a member of the db_ddladmin role in order to run the command.
/*The identity property is used for
automatically increment/ assign value for each new row inserted*/
--We can create a table with in TestDB Data base.
use TestDB
GO
IF OBJECT_ID('IdentityTable') IS NOT NULL
DROP TABLE
IdentityTable
GO
CREATE TABLE
IdentityTable (
TheIdentity
INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
TheValue
NVARCHAR(20) NOT NULL )
GO
--- insert a record with identity values. then we will get
an error message.
INSERT IdentityTable(TheIdentity, TheValue)
VALUES (1, 'First Row')
GO
-- When we try to insert the record we will get below
error.
Msg 544, Level 16, State 1, Line 3
Cannot insert explicit value for identity column in table 'IdentityTable' when IDENTITY_INSERT is set to OFF.
INSERT IdentityTable(
TheValue)
VALUES ( 'Seconds Row')
INSERT IdentityTable(
TheValue)
VALUES ( 'Third Row')
INSERT IdentityTable(
TheValue)
VALUES ( 'Fourth Row')
select * from IdentityTable
--Result
TheIdentity TheValue
1 Seconds Row
2 Third Row
3 Fourth Row
SET IDENTITY_INSERT
IdentityTable ON
INSERT IdentityTable(
TheIdentity,TheValue)
VALUES ( 5,'fifth Row')
SET IDENTITY_INSERT
IdentityTable off
INSERT IdentityTable(
TheValue)
VALUES ( 'six Row')
select * from IdentityTable
To reseed the vale manually we will use the DBCC
command.
DBCC CHECKIDENT
(
table_name
[, { NORESEED | {
RESEED [, new_reseed_value ] } } ]
)
[ WITH NO_INFOMSGS ]
The following table lists conditions when DBCC CHECKIDENT does
not automatically reset the current identity value and provides methods for
resetting the value.
The seed value is the value inserted into an identity column for
the very first row loaded into the table. All subsequent rows contain the
current identity value plus the increment value where current identity value is
the last identity value generated for the table or view.
You cannot use DBCC CHECKIDENT to perform the following tasks:
·
Change the original seed value that was specified for an
identity column when the table or view was created.
·
Reseed existing rows in a table or view.
To change the original seed value and reseed any existing rows,
you must drop the identity column and recreate it specifying the new seed
value. When the table contains data, the identity numbers are added to the
existing rows with the specified seed and increment values. The order in which
the rows are updated is not guaranteed.
Whether or not any of the options are specified for a table that
contains an identity column, DBCC CHECKIDENT returns the following message for
all operations except when specifying a new seed value.
Checking
identity information: current identity value '<current identity value>',
current column value '<current column value>'. DBCC execution completed.
If DBCC printed error messages, contact your system administrator.
When DBCC CHECKIDENT is used to specify a new seed value by
using RESEED new_reseed_value, the
following message is returned.
Checking
identity information: current identity value '<current identity value>'.
DBCC execution completed. If DBCC printed error messages, contact your system
administrator.
Caller must own the schema that contains the table, or be a
member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.
The following example resets the current identity value, if it
is needed, of the specified table in the AdventureWorks2012 database.
The following example reports the current identity value in the
specified table in the AdventureWorks2012 database, and does not correct the
identity value if it is incorrect.
The following example forces the current identity value in the
AddressTypeID column in the AddressType table to a value of 10. Because the
table has existing rows, the next row inserted will use 11 as the value, that
is, the new current increment value defined for the column value plus 1.
Here are a few things that you should know about the IDENTITY
property:
·
You can use IDENTITY not only with integers, but also with any
numeric data type that has a scale of 0. This is a good thing, especially when
you need to support numbers larger than the largest one that the integer data
type can hold.
·
IDENTITY, combined with a PRIMARY KEY or UNIQUE constraint, lets
you provide a simple unique row identifier without the need to maintain it
yourself. SQL Server does the work for you.
·
Unlike Oracle's Sequence function, SQL Server doesn't provide an
independent sequencing mechanism. Instead, the sequence provided by the
IDENTITY property applies to a single table. When you need a sequencing
mechanism tzhat generates numbers for use in different tables—as, for example,
with keys that must not overlap across tables—you'll want to choose an
alternative to IDENTITY.
·
The IDENTITY property and the column it's attached to are
inseparable, which means that you can't add or remove IDENTITY from an existing
column.You can use workarounds, but these can be very slow, especially with
large tables. For example, to add IDENTITY to an existing column, you can
create a new table, copy the data from the original table, drop the original
table, and then give the new table the original table's name.You can use a
similar process to remove IDENTITY from an existing column. Remember, though,
that these activities require table downtime—which can be lengthy for large
tables—and lots of logging.
·
When you use an INSERT statement against a table that has an
IDENTITY column, the IDENTITY value increases whether the INSERT succeeds or
fails—even if it fails on a PRIMARY KEY or CHECK constraint violation. If a
transaction is rolled back, whether implicitly or explicitly, the new IDENTITY
column value isn't rolled back, so you might end up with gaps in your
sequencing even when you're not deleting any rows. This can be a problem when
you're creating sequences that can't contain gaps, such as for invoice numbers.
·
You can't update partitioned views that are based on tables that
have an IDENTITY column.
·
The TRUNCATE TABLE statement resets the IDENTITY property in the
table to the seed. To retain the IDENTITY counter, use DELETE instead of
TRUNCATE TABLE, but keep in mind that DELETE is much slower because it's fully
logged
