satya - Tuesday, January 10, 2012 1:29:38 PM

Altering a column to be an identify column is nontrivial. See this link

Altering a column to be an identify column is nontrivial. See this link

satya - Tuesday, January 10, 2012 1:31:12 PM

However you can ask the sql manager to do this

select your table
right-click
chose designer
See on right handside
choose the identify column you want

satya - Tuesday, January 10, 2012 1:32:10 PM

At this point the table definition has been modified but not saved yet

if you try to save you get an error message saying that the option is not allowed.

satya - Tuesday, January 10, 2012 1:33:44 PM

You can turn on the option by

Tools
Options
Designers
Turn off
  Prevent saving changes that require table re-creation

satya - Tuesday, January 10, 2012 1:35:19 PM

Altering an identify of a column is a destructive operation

Underneath a temp table is created and the data transferred to the temp table and original table dropped and recreated and data transfered back. So this may not work for large tables. See the research link above to see alternatives.

satya - Tuesday, January 10, 2012 1:37:08 PM

You can ask the sql manager to show you what it will do when save before saving it

Go to design view for your table
Choose the identity column
Dont't save yet
go to 
   Table designer
Choose 
   Generater Change script

satya - Tuesday, January 10, 2012 1:38:54 PM

Here is how this looks like

/*
   Tuesday, January 10, 201210:55:06 AM
   User: sa
   Server: (local)\sqlexpress
   Database: test
   Application: 
*/

/* To prevent any potential data loss issues, you should review this script in
detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_sql_statements
	(
	ID int NOT NULL IDENTITY (1, 1),
	statement_id int NULL,
	statement nvarchar(MAX) NULL,
	database_name nvarchar(50) NULL
	)  ON [PRIMARY]
	 TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_sql_statements SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_sql_statements ON
GO
IF EXISTS(SELECT * FROM dbo.sql_statements)
  EXEC('INSERT INTO dbo.Tmp_sql_statements 
         (ID, statement_id, statement, database_name)
	    SELECT ID, statement_id, statement, database_name FROM  
                 dbo.sql_statements WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_sql_statements OFF
GO
DROP TABLE dbo.sql_statements
GO
EXECUTE sp_rename N'dbo.Tmp_sql_statements', N'sql_statements', 'OBJECT' 
GO
COMMIT