Identity in SQL - sql - sql tutorial - learn sql




  • IDENTITY is used in Microsoft SQL Server to automatically insert numerical primary key values to a table as new data is inserted.
  • This is similar to the AUTO INCREMENT command in MySQL.

Syntax:

  • The syntax for IDENTITY is as follows:
CREATE TABLE TABLE_NAME 
(PRIMARY_KEY_COLUMN INT PRIMARY KEY IDENTITY ( [Initial_Value], [Interval] ), 
...);
  • where [Initial_Value] is the first value of the primary key, and [Interval] is the interval between two consecutive identity values.
  • If no [Initial_Value] or [Interval] is specified, the default for both is 1.
  • In other words, the primary key for the first row would be 1, and subsequent rows would get a primary key value that is 1 larger than the previous row.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Example:

  • Assume we want to create a table that consists of a primary key, last name, and first name. We use the following SQL:
CREATE TABLE USER_TABLE 
(Userid int PRIMARY KEY IDENTITY(2,1), 
Last_Name nvarchar(50), 
First_Name nvarchar(50));
  • Upon creation, the table is empty.
  • We insert the first value:
INSERT INTO USER_TABLE VALUES ('Washington', 'George');
  • Now the table has the following values:

Table USER_TABLE

Userid Last_Name First_Name
2 Washington George
  • Userid is 2 because we had specified the initial value to be 2.
  • Next, we insert the second value:
INSERT INTO USER_TABLE VALUES ('Jefferson', 'Thomas');
  • Now the table has the following values:

Table USER_TABLE

Userid Last_Name First_Name
2 Washington George
3 Jefferson Thomas
  • User id for the second row is 3 because it is 1 larger than the previous User id, which is 2.

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Identity in SQL