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'.
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.
ALTER TABLE MyTable
ADD ID INT IDENTITY(1,1) NOT NULL
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.
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));
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
