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
