What does "Is Identity" column property mean in SQL Server?

Question

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

What doeds this mean?

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

Thanks in advance.

Answer 1

It simply means the column uses the Identity(increment, seed) 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.

Answer 2

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

Answer 3

http://sqlskills.com/BLOGS/KIMBERLY/post/Ever-increasing-clustering-key-the-Clustered-Index-Debateagain!.aspx

Answer 4

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