How to Add Identity to Existing Column

[Database Administrator]

Hi,

I want to modify the column with Identity. But, i am getting the
following error.

ALTER TABLE ALTER COLUMN INT IDENTITY (10, 1)
--Already 10 Values are inserted in Table.

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

With Thanks,

Guru.

[Database Administrator]

AFAIK, you cannot add the IDENTITY property to an existing column.

However, you can add a new IDENTITY column to the table:

ALTER TABLE ADD INT IDENTITY (10, 1)

--
Gert-Jan
SQL Server MVP


Guru wrote:
>
> Hi,
>
> I want to modify the column with Identity. But, i am getting the
> following error.
>
> ALTER TABLE ALTER COLUMN INT IDENTITY (10, 1)
> --Already 10 Values are inserted in Table.
>
> My Error: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'IDENTITY'.
>
> With Thanks,
>
> Guru.

[Database Administrator]

I made a script that can allow you to added identity to column

http://iomegatrix.com/Phoenix/view_blog.aspx?_id_=34





"Guru" wrote:

> Hi,
>
> I want to modify the column with Identity. But, i am getting the
> following error.
>
> ALTER TABLE ALTER COLUMN INT IDENTITY (10, 1)
> --Already 10 Values are inserted in Table.
>
> My Error: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'IDENTITY'.
>
> With Thanks,
>
> Guru.
>

[Database Administrator]

That's an impressive script, but I see various problems:

(a) there is something like :

' ADD [' + @columnName + '] ' + @dataType + ' IDENTITY(1?) NOT NULL')

I don't know what that strange sigma-looking symbol inside the parens is;
SQL Server won't buy it, either.

(b) why do you assume the IDENTITY should suddenly be both PRIMARY KEY and
CLUSTERED? Shouldn't you check if the existing column is a primary key, or
has a clustered index, and that the existing data is eligible to be a
primary key (e.g. no nulls, no duplicates)?

(c) what about check constraints, default constraints, and indexes on the
old table?

(d) the code may work right now, but is not very future-proof. There are
all kinds of features you need to be wary of, for example if you try this
code in SQL Server 2008 against a table that has row/page compression turned
on, or CDC or Change Tracking, or sparse columns... you could be in for a
big surprise. :-)







On 5/7/08 1:10 PM, in article
98CE293E-CC57-4F30-98AB-6FB969D17244@microsoft.com, "TJ"
wrote:

> I made a script that can allow you to added identity to column
>
> http://iomegatrix.com/Phoenix/view_blog.aspx?_id_=34
>
>
>
>
>
> "Guru" wrote:
>
>> Hi,
>>
>> I want to modify the column with Identity. But, i am getting the
>> following error.
>>
>> ALTER TABLE ALTER COLUMN INT IDENTITY (10, 1)
>> --Already 10 Values are inserted in Table.
>>
>> My Error: Msg 156, Level 15, State 1, Line 1
>> Incorrect syntax near the keyword 'IDENTITY'.
>>
>> With Thanks,
>>
>> Guru.
>>

[Database Administrator]

On Wed, 07 May 2008 13:24:21 -0400, Aaron Bertrand [SQL Server MVP]
wrote:

>That's an impressive script, but I see various problems:
>
>(a) there is something like :
>
>' ADD [' + @columnName + '] ' + @dataType + ' IDENTITY(1?) NOT NULL')
>
>I don't know what that strange sigma-looking symbol inside the parens is;
>SQL Server won't buy it, either.

Hi Aaron,

My guess is that some "smart" logic in the blog software he uses is
programmed to replace ",1" with that symbol. Probably makes sense for
people blogging in a language that uses this symbol but don't have it on
their keyboards.

An even bigger problem with his script is that he fails to enclose all
modifications in a transaction. As such, any error or external event
(like loss of power) during execution of the script could potentially
leave the database in an inconsistent state...

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Database Administrator]

> My guess is that some "smart" logic in the blog software he uses is
> programmed to replace ",1" with that symbol. Probably makes sense for
> people blogging in a language that uses this symbol but don't have it on
> their keyboards.

....but not so much sense for people who will be posting T-SQL code samples,
which will almost certainly contain these phrases that the "smart" function
is looking for. :-)

> An even bigger problem with his script is that he fails to enclose all
> modifications in a transaction. As such, any error or external event
> (like loss of power) during execution of the script could potentially
> leave the database in an inconsistent state...

Agreed.

Other things that have come to mind since are whether the table had computed
columns (persisted or not), was on a filegroup other than PRIMARY, had
TEXTIMAGE on a filegroup other than primary, was participating in
replication, had a non-default fill factor, had a timestamp column, was
partitioned, XML columns, etc. etc. ad nauseum.

By the time he adds all these missing things, SQL Server 2008 will be out,
and there will be a whole new mess of things that will need to be added --
some of which I mentioned before, and some of which I didn't bother. :-)

A.

[Database Administrator]

Hi,

You can use Enterprise Manager on SQL 2000 to change a column to an
identity or remove identity by using the design table feature and
changing the property at the bottom.

Cheers,

Gary.

*** Sent via Developersdex http://www.developersdex.com ***

[Database Administrator]

> You can use Enterprise Manager on SQL 2000 to change a column to an
> identity or remove identity by using the design table feature and
> changing the property at the bottom.

But have you scripted out what this actually does? It doesn't just add the
property. It creates a new table, copies the data, drops the old table,
renames the new one... and good luck with that on a large table. =)

[iva_registratio]

Remove the old column and add new one.

--SQL Script:
ALTER TABLE Users DROP PK_Users
GO
ALTER TABLE Users DROP COLUMN UserID
GO
ALTER TABLE Users ADD UserID int NOT NULL IDENTITY(1,1) 
GO
ALTER TABLE Users ADD CONSTRAINT PK_Users PRIMARY KEY CLUSTERED ([UserID] ASC)
GO

This is simple enough and it works for me

Originally Posted by Gert-Jan Strik

AFAIK, you cannot add the IDENTITY property to an existing column.

However, you can add a new IDENTITY column to the table:

ALTER TABLE ADD INT IDENTITY (10, 1)

--
Gert-Jan
SQL Server MVP


Guru wrote:
>
> Hi,
> I want to modify the column with Identity. But, i am getting the
> following error.
> ALTER TABLE ALTER COLUMN INT IDENTITY (10, 1)
> --Already 10 Values are inserted in Table.
>
> My Error: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'IDENTITY'.
>
> With Thanks,
> Guru.