Auto Increment Field or Identity Field In MS SQL Server

Posted by Akhil under Sql Server on 3/4/2011 | Points: 10 | Views : 12668 | Status : [Member] | Replies : 4
Creating a Auto Increment field in SQL Server allows a unique number to be generated when a new record is inserted into a table.

Syntax for creating an Auto Increment field in SQL Server.

CREATE TABLE [dbo].[Company](
[CompanyId] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](200) NOT NULL,
[Address] [nvarchar](max) NOT NULL,
[Phone] [nvarchar](max) NULL,
[autoBackup] [bit] NOT NULL,
[applycreditlimit] [bit] NULL,
[EmailId] [nvarchar](max) NULL,
[salesTaxPercentage] [numeric](18, 2) NULL)
Here, CompanyId has been set as an Auto Increment Id.
A column which has datatype int, big int, tiny int or small int can only be set as an Auto Increment field.
Suppose, you would like to set the AutoIncrement field (CompanyId) at the remote databases to only use the next number free AFTER the 100.

Here, CompanyId has been set as an Auto Increment Id.
A column which has datatype int, big int, tiny int or small int can only be set as an Auto Increment field.
Suppose, you would like to set the AutoIncrement field (CompanyId) at the remote databases to only use the next number free AFTER the 100.

There's an "Identity Seed" parameter that you can specify (either through SQL or through the table designer in Enterprise Manager) that will let you set the base value for an Identity/AutoNumber field to the value you want. Any new Identity values will be incremented from this base (using the "Identity Increment" value which you can specify in the same place to determine how much to grow the value by).

If you would like to see a video as to how to set the auto increment field in SQL Server, then we have a video tutorial uplaoded at:

http://www.industrialtrainingkolkata.com/?cat=1




Responses

Posted by: Jayakumars on: 3/4/2011 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi

if You need how to auto increment create table Query below use this query



USE [Test]
GO
/****** Object: Table [dbo].[tb1] Script Date: 03/04/2011 14:38:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tb1](
[id] [int] IDENTITY(1,1) NOT NULL,
[EName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tb1] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF



Mark as Answer if its helpful to you

Akhil, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Akhil on: 3/5/2011 [Member] Starter | Points: 25

Up
0
Down
Auto Increment Field or Identity Field In MS SQL Server

Creating a Auto Increment field in SQL Server allows a unique number to be generated when a new record is inserted into a table.

Syntax for creating an Auto Increment field in SQL Server.

CREATE TABLE [dbo].[Company](
[CompanyId] [bigint] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](200) NOT NULL,
[Address] [nvarchar](max) NOT NULL,
[Phone] [nvarchar](max) NULL,
[autoBackup] [bit] NOT NULL,
[applycreditlimit] [bit] NULL,
[EmailId] [nvarchar](max) NULL,
[salesTaxPercentage] [numeric](18, 2) NULL)
Here, CompanyId has been set as an Auto Increment Id.
A column which has datatype int, big int, tiny int or small int can only be set as an Auto Increment field.
Suppose, you would like to set the AutoIncrement field (CompanyId) at the remote databases to only use the next number free AFTER the 100.

Here, CompanyId has been set as an Auto Increment Id.
A column which has datatype int, big int, tiny int or small int can only be set as an Auto Increment field.
Suppose, you would like to set the AutoIncrement field (CompanyId) at the remote databases to only use the next number free AFTER the 100.

There's an "Identity Seed" parameter that you can specify (either through SQL or through the table designer in Enterprise Manager) that will let you set the base value for an Identity/AutoNumber field to the value you want. Any new Identity values will be incremented from this base (using the "Identity Increment" value which you can specify in the same place to determine how much to grow the value by).

If you would like to see a video as to how to set the auto increment field in SQL Server, then we have a video tutorial uplaoded at:

http://www.industrialtrainingkolkata.com/?cat=1


Akhil, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Karthikanbarasan on: 3/5/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down
Check the below links...

http://www.w3schools.com/Sql/sql_autoincrement.asp

http://www.kavoir.com/2009/01/create-auto-increment-column-in-mysql.html

Thanks
Karthik
www.f5Debug.net

Akhil, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriramnandha on: 5/23/2012 [Member] Starter | Points: 25

Up
0
Down
CREATE TABLE STUDYLIST(STUDYID INT IDENTITY(1,1),STUNAME VARCHAR(90))

HOPE THIS WILL HELP

REGARDS

sriram

Akhil, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response