Adding Index to existing table

[luvmy2Js]

I have a table called "PersonalData".

There are 3 fields:

FirstName, LastName, Salary..

I need to add an index key to the table in order to make the query I am using work. How to I go about adding "ID" as a primary key and making it an index so that it auto-generates the number (1,2,3,4 etc..)

Thanks for any help on this!!

 

[luvmy2Js]

I have this:

ALTER TABLE allowancesdata
ADD (ID int primarykey identity);

but getting error: Incorrect syntax near '('.

 

[markros]

I think you may want to create a new table

create table PersonalDataNew (ID int identity(1,1) primary key, FirstName varchar(30), LastName varchar(50), Salary money, etc.)

insert into PersonalDataNew select * from PersonalData

drop table PersonalData

execute sp_rename 'PersonalDataNew', 'PersonalData'

From the top of my head idea only.

PluralSight Learning Library

 

[gmmastros]

ALTER TABLE allowancesdata
ADD (ID int primarykey identity);

Your syntax is close, but not quite right.

ALTER TABLE allowancesdata
 ADD ID int Identity(1,1) primary key

The syntax shown above should be correct. The problem is.... you cannot control the order in which rows will get the ID value. If this is important to you, then you'll need to use markros's idea.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[luvmy2Js]

Thanks, got it..