What's the code in turning an ID into an autonumber data type?

Question

I am using a visual studio 2010 and Microsoft SQ'L Server 2005.

I want my text box Equip_No to be an auto number data type.

I already followed these steps

Set the properties:

Identity Specification = Yes
(Is Identity) = Yes
Identity Increment = 1
Identity Seed = 1

Please help me. Any help will be appreciated. Thanks!

I already tried it but my text box isn't showing any default value. I want it to have a default value whenever I run it or after I add a data to the database. — Ash917, Apr 29 '14 at 05:44
Do you have SQL access, or do you have to use VS2010 code to do this? You could employ this solution: http://stackoverflow.com/questions/2169080/mysql-alter-a-column-to-be-auto-increment — Vesper, Apr 29 '14 at 11:41

t-clausen.dk · Accepted Answer · 2014-04-30T07:56:15.823

I don't think you can alter an existing column into identity in Sqlserver-2005.

You can however add a column with identity and after that manipulate the values and change the seed

Adding the autoincremental column

ALTER TABLE yourtable 
ADD newid INT identity(1,1)

Making it possible to change your values in your id column however you want

SET IDENTITY_INSERT yourtable ON

Do your manipulating here, example:

UPDATE yourtable SET newid = id

Cleanup

SET IDENTITY_INSERT yourtable OFF

DBCC CHECKIDENT
 ('YourTable', RESEED, <a value at least 1 higher than the max existing value>);

Replacing old column

ALTER TABLE yourtable DROP COLUMN id
SP_RENAME 'yourtable.newid', 'id', 'COLUMN'

1 Answers1