how to make an existing table field auto-increment in MsSQL

Question

hi I am using Ms SQL 2014. Please find below the table I have created. My problem is that I do not know how to make my primary key auto-increment from here. There isn't a script file for me to load to my surprise. Another thing is that I have 2 copies of MS SQL - one is the SQLExpress and the other one is the main one. Not sure why it is so. Hope someone can advise me on the above. Tks.

Midhun T P · Answer

Hi there's nothing wrong with your mssql. It is a common warning you are getting. Just follow below link to find out the work around - https://support.microsoft.com/en-in/kb/956176

Nitin Sontakke · Answer

Go to Tools -> Options - Designers And uncheck Prevent saving changes that require table re-creation and you should be okay. This to protect accidental table drop and create in production (or any other for that matter) environments. Please google the term to understand the full impact of unchecking that option and please do it at your own risk.

Tangara G · Answer

Hi Nitin Tks so much for your reply. Yes I could see the column you mentioned. However after I did what you said and wanted to save the latest and MsSQL gave me the following message : Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created. Do I have to drop the table and create again ? or is there something wrong with my Ms SQL?

Midhun T P · Answer

Hi Please check below link - http://stackoverflow.com/questions/10991894/auto-increment-primary-key-in-sql-server-management-studio-2012

Nitin Sontakke · Answer

Can you see the Column Properties section in your screen-shot? Just scroll down a bit in that and you will see Identity Specifications. Make that Yes and provide Seed and increment values typically 1 and 1. Hope it helps!