Go Back

identity in sql and define SQL Server Identity

All Articles

#sql #what is identity in sql #what is identity in sql serve #identity_in_SQL

identity in sql and define SQL Server Identity

Identity Column in sql 

Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed

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


Example of Identity column in SQL

    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (Personid)

We can also cusmized increament value in SQL 

below is sntext for this :

IDENTITY (data_type [ , seed , increment ] ) AS column_name  

Seed: Starting value of a column. 
      Default value is 1.

Here the example of  Identity column  in sql 

USE DeveloperIndian;
SELECT  IDENTITY(int, 90000, 1) AS ProductId,  
        Name AS pd_name,  
INTO DeveloperIndian.SpecialProduct
FROM DeveloperIndian.Product
-- Display new table
SELECT * FROM DeveloperIndian.SpecialProduct;