Add a IDENTITY to a column in SQL SERVER 2008

Question

create table stud(
Student_Id int primary key,
Student_Name varchar(30),
Student_surname varchar(12),
Student_Initial varchar(10))

I had created a table stud. Now i want to add Identity to Student_Id column using alter query

alter table stud alter column student_Id int identity   

I get error as

Incorrect syntax near the keyword 'identity'.

Answer 1

You cannot make an already existing column as an IDENTITY column. Either you drop and recreate the table with the column marked as IDENTITY', or drop the column and add a newIDENTITY` column.

Answer 2

ALTER TABLE MyTable
  ADD ID INT IDENTITY(1,1) NOT NULL

Answer 3

Syntax:

IDENTITY [ (seed , increment) ]

alter your table like as this:

create table stud( 
Student_Id int IDENTITY(1,1) primary key,
Student_Name varchar(30), 
Student_surname varchar(12), 
Student_Initial varchar(10));

Answer 4

If Stud contains data, you could always make a shadow table, e.g. Stud2, which contains the Identity column, then run

ALTER TABLE dbo.stud SWITCH TO dbo.stud2

Then you can reseed Stud2, drop Stud, and rename Stud2 to Stud.

That way you can keep the data while dropping/recreating the table with Identity.

Answer 5

you can use below query to set identity

CREATE TABLE [dbo].[stud](
    [Student_Id] [int] IDENTITY(1,1) NOT NULL,
    [Student_Name] [varchar](30) NULL,
    [Student_surname] [varchar](12) NULL,
    [Student_Initial] [varchar](10) NULL,
PRIMARY KEY CLUSTERED 
(
    [Student_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

SET ANSI_PADDING OFF GO