Changing the seed and increment values on the identity column

Question

Post reply

Changing the seed and increment values on the identity column

raja_saminathan

SSCommitted

Points: 1607
More actions
May 9, 2008 at 6:24 am

#380392

Hi,
I want to change the Identity increment value of a column, I Tried with the following command,it returns error message --- 'Incorrect syntax near the keyword 'IDENTITY'.
ALTER TABLE MyCustomers ALTER COLUMN CustId IDENTITY (200, 2);
Expecting your ideas to solve this problem
Thanks and Regards,
Rajesh

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Zahran SSCommitted Points: 1724 More actions · Answer 1

Try this,

DBCC CHECKIDENT('MyCustomers', RESEED, 200)

- Zahran -

raja_saminathan SSCommitted Points: 1607 More actions · Answer 2

Hi Zahran,

The Code You Suggested will only change the identity start value,but i want to change the identity increment value by other increment value.

For eg: Currently, i have an identity _incr of 1 , i want to change the identity_incr to 2

Hope this will give you a clear Picture

Awaiting your replies

Rajesh

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

What is usually done in these cases is to create a temporary table mirroring your existing table, with the correct identity seed and increment, then copy the data over, and then drop the existing table and rename the temp table to your original name...

Here's an example:

CREATE TABLE dbo.Tmp_bob

(

id int NOT NULL IDENTITY (2, 200)

) ON [PRIMARY]

GO

SET IDENTITY_INSERT dbo.Tmp_bob ON

GO

IF EXISTS(SELECT * FROM dbo.bob)

EXEC('INSERT INTO dbo.Tmp_bob (id)

SELECT id FROM dbo.bob WITH (HOLDLOCK TABLOCKX)')

GO

SET IDENTITY_INSERT dbo.Tmp_bob OFF

GO

DROP TABLE dbo.bob

GO

EXECUTE sp_rename N'dbo.Tmp_bob', N'bob', 'OBJECT'

There doesn't seem to be a straightforward way to change the increment.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

raja_saminathan SSCommitted Points: 1607 More actions · Answer 4

Many Thanks for your Kind Information

so,now i think it is better to create another table instead of changing the identity increment as you suggested

Regards,

Rajesh

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 5

I got the same error. It seems that we cannot do it in either SQL 2000 or SQL 2005, although in BOL claims we can do it in SQL 2005.

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

SQL ORACLE (5/9/2008)
I got the same error. It seems that we cannot do it in either SQL 2000 or SQL 2005, although in BOL claims we can do it in SQL 2005.

You mean this article in BOL?

http://msdn.microsoft.com/en-us/library/ms174123.aspx

I fell for that shortly today, when I realized that it was only for SQL Server CE....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 7

In my memory, we cannot alter a column to IDENTITY, not for CE.:hehe:

Marc Bamberg SSC Enthusiast Points: 142 More actions · Answer 8

This is a big problem for me on a very large table that uses the identity as a key.

On a 7/24 OLTP system dropping and recreating the table causes a significant outage.

If you think this issue should be addressed by MS you can add your voice to this suggestion made to MS:

https://connect.microsoft.com/SQLServer/feedback/details/560513/change-increment-values-on-an-identity-column

Thanks,

Marc

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 9

I have not tried it myself. Just idea:

There is a table called sys.identity_columns

It contains the details of all identity columns.

Try to change it!

You will need to use DAC and run

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 10

My stupid laptop keyboard...

To update system catalogs, you will need to use DAC and run server in single-user mode.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Marc Bamberg SSC Enthusiast Points: 142 More actions · Answer 11

Marc Bamberg

SSC Enthusiast

Points: 142

May 21, 2010 at 3:29 pm

#1171127

Thanks for the tip I wil check it out.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 12

Sorry, it most likely will not work as it looks like since 2005 sp2 updates to sys catalogs are not allowed (special thanks to MS as they always now better:-)).

I've didn't tried to update systables for so long...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Marc Bamberg SSC Enthusiast Points: 142 More actions · Answer 13

Thanks for letting me know. While it would have been interesting to try, updates to the sys catalogs aren't something that we would likely implement into a production system.