21

I am using SQL Server for the first time and I see that a column property is called Is Identity.

What does this mean?

What are the advantages of marking a column property as Is Identity = Yes?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Niyaz
  • 53,943
  • 55
  • 151
  • 182

3 Answers3

29

It simply means the column uses the Identity(seed, increment) function to provide values for a primary key (usually). It is also known as "Autonumber". The second line below is an example:

CREATE TABLE Table (
TableID bigint IDENTITY(1,1) NOT NULL,
DateTimeStamp datetime NOT NULL DEFAULT (getdate()),
Data nvarchar(100) NOT NULL,
CONSTRAINT PK_Table PRIMARY KEY CLUSTERED 
(
    TableID ASC
)

It acts as a default value for the column that increments for each record. Note that you can also get the value inserted from SCOPE_IDENTITY(). Do not use @@IDENTITY as it is depreciated and can return the wrong result in the case of triggers or nested contexts.

Vishwanath Dalvi
  • 35,388
  • 41
  • 123
  • 155
Godeke
  • 16,131
  • 4
  • 62
  • 86
4

Flag indicating an Identity Column - can be used as an auto-increment column (recommended for any table)

it has many implications like being able to get the id of the last inserted row in a table using @@IDENTITY or SCOPE_IDENTITY() etc.

Try: Understanding Identity Columns

IEnumerator
  • 2,960
  • 5
  • 31
  • 33
  • i updated comment with some more information. don't forget to vote up the answer. Thanks – IEnumerator Feb 14 '09 at 05:30
  • 1
    Don't use @@IDENTITY. It's unreliable, if there is a trigger on the table which puts data into another table which has an identity column on it @@IDENTITY will return the wrong value. – mrdenny Feb 14 '09 at 07:34
1

It's equivalent to MySQL's AUTO_INCREMENT property. Usually used on a primary key column

Adam Pope
  • 3,234
  • 23
  • 32