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

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×