Sql Server 2000 - Incorrect syntax near the keyword 'IDENTITY'

Incorrect syntax near the keyword 'IDENTITY'

Let us say there is a table in sql server 2000 which does not have a identity key, you inspect it manually and find out that there is a column which is unique and you can make it in to an identity column and decide to go and open your Query analyzer and try to do an alter table add identity something like this,

ALTER TABLE [dbo].[projects] ALTER COLUMN [id] IDENTITY(100, 1) Not Null

Here i want the seed to start at 100 and increment by 1 since i got 99 rows already with proper ids.

But this would not run properly, we will get an error something like,
Incorrect syntax near the keyword 'IDENTITY'

There is no problem in the syntax the issue is sql 2000 cannot add an identity just like that to an column since there is lot of other background work that needs to be done.

So it is not possible to do it via single T-Sql statement.

But you can add the entire identity via enterprise manager very easily,

Just right click on the table >> design table >> click on the id column >> make identity = yes in the columns property area below and then set the identity seed (starting value) and the identity increment.

When you do this, this is what enterprise manager does in the background,

BEGIN TRANSACTION

SET QUOTED_IDENTIFIER ON

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

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

CREATE TABLE dbo.Tmp_projects

(

id int NOT NULL IDENTITY (101, 1),

name varchar(50) NULL

) ON [PRIMARY]

GO

SET IDENTITY_INSERT dbo.Tmp_projects ON

GO

IF EXISTS(SELECT * FROM dbo.projects)

EXEC('INSERT INTO dbo.Tmp_projects (id, name)

SELECT id, name FROM dbo.projects TABLOCKX')

GO

SET IDENTITY_INSERT dbo.Tmp_projects OFF

GO

DROP TABLE dbo.projects

GO

EXECUTE sp_rename N'dbo.Tmp_projects', N'projects', 'OBJECT'

GO

ALTER TABLE dbo.projects ADD CONSTRAINT

Myuniqye UNIQUE NONCLUSTERED

(

id

) ON [PRIMARY]



GO

COMMIT

If you look at the above, it is basically creating a temporary table and inserting all the records from the main table and then renaming the temp table to original table name.

The problem is many projects want developers to save the script changes so that they can apply the same on to development / test / production servers, so developers want to save the script.

There is a way to do that too, actually 2 ways,

1) In Enterprise manager when you click design table and after you made the change, there is a small icon at the top which reads,"save changes script" (should be the third icon from the left if you have icons unmodified), so this basically scripts the entire change for you to save it.

2) Start Sql Profiler, perform the operation and then save changes script is the second way to do it.

This link too verifies the same,

http://www.sqlmag.com/Article/ArticleID/22080/sql_server_22080.html