How to create an auto increment primary key

Question

_{Thursday, February 25, 2010 9:15 AM}

I have created a table using SQL Server Enterprise Manager, but I have no idea how to create / modify a field to become an auto increment primary key with command way or SQL server enterprise manager way. Any idea?

All replies (7)

_{Thursday, February 25, 2010 9:20 AM ✅Answered}

CREATE TABLE [dbo].[x](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [text] [nchar](10) NOT NULL,
 CONSTRAINT [PK_x] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
insert into x values ('one') go insert into x values ('two') 

My Blog - MSDN Complement by providing Visual C# Walkthroughs and Sample Codes - Founded In February 24, 2010

_{Thursday, February 25, 2010 9:59 AM ✅Answered}

AUTO_INCREMENT is not valid TSQL on Sql Server
Replace it with

IDENTITY


(1,1)

Try the whole thing as :

CREATE TABLE staffsaleorder2
(
orderID int IDENTITY(1,1) not null,
badgeno varchar(10),
itemcode varchar(10),
orderquantity int,
price decimal(9),
itemdescription varchar(100),
time timestamp(8),
CONSTRAINT [PK_staffsaleorder2] PRIMARY KEY CLUSTERED 
(
    [orderID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 

HTH Ciaran http://wannabedeveloper.spaces.live.com

_{Thursday, February 25, 2010 10:00 AM ✅Answered}

Sql Server doesn't have AUTO_INCREMENT keyword. you should use IDENTITY Property instead which is described @ Here

My Blog - MSDN Complement by providing Visual C# Walkthroughs and Sample Codes - Founded In February 24, 2010

_{Thursday, February 25, 2010 10:06 AM ✅Answered}

AUTO_INCREMENT is not the correct syntax. If you look at Mr. Help's example the syntax is IDENTITY(1,1)

Matt

_{Friday, February 26, 2010 10:04 AM ✅Answered}

Yes of course

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_x
    (
    id int NOT NULL IDENTITY (1, 1),
    text nchar(10) NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_x SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_x ON
GO
IF EXISTS(SELECT * FROM dbo.x)
     EXEC('INSERT INTO dbo.Tmp_x (id, text)
        SELECT id, text FROM dbo.x WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_x OFF
GO
DROP TABLE dbo.x
GO
EXECUTE sp_rename N'dbo.Tmp_x', N'x', 'OBJECT' 
GO
ALTER TABLE dbo.x ADD CONSTRAINT
    PK_x PRIMARY KEY CLUSTERED 
    (
    id
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

My Blog - MSDN Complement by providing Visual C# Walkthroughs and Sample Codes - Founded In February 24, 2010

_{Thursday, February 25, 2010 9:52 AM}

What's wrong!?

CREATE TABLE staffsaleorder2
(
orderID int not null AUTO_INCREMENT,
badgeno varchar(10),
itemcode varchar(10),
orderquantity int,
price decimal(9),
itemdescription varchar(100),
time timestamp(8)
PRIMARY KEY (orderID)
)

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'AUTO_INCREMENT'.

I have no idea!

_{Friday, February 26, 2010 1:28 AM}

AUTO_INCREMENT is not the correct syntax. If you look at Mr. Help's example the syntax is IDENTITY(1,1)

Matt

I see, is there a way to do that through keyword like "modify" instead of making anew table? I always have a table.