SQL Server won't let me make column identity

Question

So I have a table in SQL Server w/ a primary key column, and 4 other columns. When I modify the table, and select the primary key column to be identity, it won't let me save the table.

How can I make it an identity column through T-SQL or something without going to the UI?

Thanks.

Here's the create

USE [db]
GO

/****** Object:  Table [dbo].[tblMessages]    Script Date: 04/05/2011 11:58:25 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblMessages](
    [messageId] [int] NOT NULL,
    [messageText] [varchar](500) NOT NULL,
    [messageLatitude] [float] NOT NULL,
    [messageLongitude] [float] NOT NULL,
    [messageTimestamp] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [messageId] 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

SET ANSI_PADDING OFF
GO

How to do this with code when you have data in your table. http://stackoverflow.com/questions/288222/how-do-i-add-the-identity-property-to-an-existing-column-in-sql-server. Or you can use SSMS which will drop and recreate everything for you. — Mikael Eriksson, Apr 05 '11 at 16:13

score 2 · Accepted Answer · answered Apr 05 '11 at 16:04

2

You cannot turn an existing column into an IDENTITY column after it's been created.

ALTER TABLE dbo.YourTable
  ALTER COLUMN YourColumn INT IDENTITY

will cause an error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'IDENTITY'.

You need to create a new column of type INT IDENTITY and then possibly drop the old one. Or if your table is still empty: drop it and re-create it with the correct settings for your ID column

answered Apr 05 '11 at 16:04

marc_s

732,580
175
1,330
1,459

For SQL Server 2008 it is possible [see my answer here](http://stackoverflow.com/questions/6084572/how-to-set-auto-increment-after-creating-a-table-without-any-data-loss/6086661#6086661). – Martin Smith May 22 '11 at 17:00
@Martin: OK, thanks - good to know - even though it feels like a bit of a messy hack... – marc_s May 22 '11 at 20:03
Indeed but it is undoubtedly the best solution for large tables as it is more or less instant as opposed to making a load of logging and locking activity. – Martin Smith May 23 '11 at 08:27

score 1 · Answer 2 · answered Apr 05 '11 at 15:59

1

ALTER TABLE MyTable
ADD NewIdentity INT IDENTITY;

ALTER TABLE MyTable
DROP COLUMN OldPK;

EDIT

If your table is empty, just drop it and add IDENTITY after INT on your PK column and be done with it.

answered Apr 05 '11 at 15:59

JNK

63,321
15
122
138

+1 you got it right even before I posted - I just misread your answer first ..... – marc_s Apr 05 '11 at 16:08
@Marc - you provided an explanation on the *why* though, which I skipped. – JNK Apr 05 '11 at 16:09
well, I just quoted the Microsoft error message - still doesn't really explain **why** this isn't possible (would be nice if it were!) – marc_s Apr 05 '11 at 16:10
@marc - I'm guessing because it would require tracking historical values to generate an accurate seed value, which SQL doesn't do unless you tell it to – JNK Apr 05 '11 at 16:12

SQL Server won't let me make column identity

2 Answers2