0

I have created a table with following query,

CREATE TABLE [dbo].[Table_A](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Value] [nvarchar](50) NULL,
 CONSTRAINT [PK_Table_A] PRIMARY KEY CLUSTERED 
   ([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

Currently its appearing like this

enter image description here

I'm trying to change above to below one

enter image description here

Since I cannot do it using SQL Server management studio, Now I'm trying to set this identity off with following query 

SET IDENTITY_INSERT Table_A OFF

Query running properly but its stays same

kez
  • 2,273
  • 9
  • 64
  • 123
  • `SET IDENTITY_INSERT Table_A OFF` is just temporary so you can insert your own values. – Mark Rotteveel Jan 15 '17 at 16:23
  • how can I update above table by updating is identity No – kez Jan 15 '17 at 16:24
  • you will need to create a new table to `how can I update above table by updating is identity No` – TheGameiswar Jan 15 '17 at 16:25
  • If your table is large, I recommend the partition `SWITCH` method mentioned in http://stackoverflow.com/questions/8230257/remove-identity-from-a-column-in-a-table. – Dan Guzman Jan 15 '17 at 16:51
  • This is probably because SSMS isn't letting you save changes to the table that require recreating the table. On the **Tools** menu, click **Options**. > In the navigation pane of the Options window, click **Designers**. > **un-check** the Prevent saving changes that require the table re-creation check box, and then click **OK**. [Microsoft Support](https://support.microsoft.com/en-us/kb/956176) – SqlZim Jan 15 '17 at 17:11

0 Answers0