Suppose we have Order tables which consist of Id, CustomerId, StoreId, GrossTotal and discount.
Create table Orders ( ID INT PRIMAR KEY, CUSTOMERID NVARCHAR(100), STOREID NVARCHAR(10), GROSSTOTAL DECIMAL, TOTALORDERDISCOUNT DECIMAL )
Here we have Id as a primary key column but it’s now an identity column. An identity column is a self-incrementing column whose value is incremented as per rule defined by the user.
In order to check whether the column is Identity column or not just go to the Table column and select the column to which you want to check isIdentity column or not and right click on the column.
If we don’t have an identity column than we have to supply the value to all columns as shown below in case of Orders table:
INSERT INTO Orders VALUES(3,'123456789','3267',10000,300)
The basic use of having a primary key column is to uniquely identify the each record in this column. So if I declare the column as identity the values inserted will unique and I don’t have to insert values to that column.
Now let’s create a new table for order with ID defined as Identity.
Create table Orders ( ID INT PRIMARY KEY IDENTITY(1,1), CUSTOMERID NVARCHAR(100), STOREID NVARCHAR(10), GROSSTOTAL DECIMAL, TOTALORDERDISCOUNT DECIMAL, ORDERCREATEDDATE DATETIME )
Here I have declared Id as an n Identity column which means it’s a self-incrementing column we have defined it as (1,1) which significantly denotes= (start number, IncrementBy) i.e. we will have our first Id as 1 and then increment by 1.
Defining Identity using Designer mode
Properties in Identity Specification: We are specifying SQL server to automatically the value of this column based on Increment and seed.
Identity Increment: How much increment should be done to Seed Value each time you insert the records?
Identity Seed: What number should we start with?
Once we are done setting the identity column we can check the column is-Identity or not same as shown before:
Now to insert into this table we don’t have to supply value for Id as before:
INSERT INTO Orders VALUES('123456789','3267',10000,300,getdate())
Records inserted with Id set to 1
If we delete a record from the Identity column and try to insert the same records into the table, a new Id will be created for the same which will result in absence of the deleted Id in the table as shown below:
These are the set of Orders present in my Order table:
Now if I try to delete The Id 1 from the table, there will be now recorded present in the table for Id=1 and if try to insert the new records a new Id will be created.
In the certain scenario, we delete the records in Table to test our application in development environment resulting in ambiguous records which are not sequential. So in order to reuse those records in Identity column, we can take following below steps:
So now I want to insert an order an Id 1 which we have deleted above, so in order to insert into the Identity column we have to enable
IDENTITY INSERT ON
That will specify the SQL server we are passing the Id columns value explicitly.
If we want to disable Identity Insert on:
SET IDENTITY_INSERT ORDERS OFF
Now, if we have deleted all the rows from the table and if I try to insert the new records it will take the last value of the Identity column. So in that scenario, we can either truncate the table which will re-initialize the Identity seed value to initial or we will run DBCC
Command to reset the Identity value