Menu
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?
869
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.
An identity column is a numeric column in a table that is automatically populated with an integer value each time a row is inserted. Identity columns are often defined as integer columns, but they can also be declared as a bigint, smallint, tinyint, or numeric or decimal as long as the scale is 0.
The IDENTITY property allows you to specify a counter of values for a specific column of a table. Columns with numeric data types, such as TINYINT, SMALLINT, INT, and BIGINT, can have this property. The Database Engine generates values for such columns sequentially, starting with an initial value.
An identity column differs from a primary key in that its values are managed by the server and usually cannot be modified. In many cases an identity column is used as a primary key; however, this is not always the case.
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.
129
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
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
