How to change Increment Value on IDENTITY column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to change Increment Value on IDENTITY column

Author

Topic

Kristen
Test

22859 Posts

Posted - 2011-08-17 : 11:28:58

Can I change the increment_value on an identity column without drop/recreate the table?

I've created a column (which has gone into production

] as


	MyColumn int NOT NULL IDENTITY (1, 10000),

instead of


	MyColumn int NOT NULL IDENTITY (10000, 1),

FWIW I've added the following to our QA Pre-Release checks to catch it if it happens again:


SELECT	
	o.type,
	[seed_value] = CONVERT(int, seed_value),
	[increment_value] = CONVERT(int, increment_value),
	[last_value] = CONVERT(int, last_value),
	[Name] = O.name + '.' + C.name
FROM	sys.identity_columns AS C
	JOIN sys.objects AS O
		ON C.object_id  = O.object_id
WHERE	1=1
	AND
	(
		   seed_value <> 10000	-- Normal starting value
		OR increment_value <> 1
	)
	AND O.name NOT LIKE 'TEMP[_]%'	-- Ignore TEMP_xxx tables
	AND O.name NOT IN ('dtproperties', 'IgnoreThisTableName')
	AND O.type = 'U'
--
ORDER BY increment_value DESC, seed_value

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-17 : 13:06:14

If ordinal position of the column is not a consideration, can you drop and recreate the column instead of dropping and recreating the table?

If the table is in production, that may not be an acceptable solution, but at least you wouldn't have to backup and restore the data in the other columns.

Kristen
Test

22859 Posts

Posted - 2011-08-17 : 15:52:06

Don't think I can get away with that (sadly)?

IDENTITY column is the PK and also target of numerous FKeys ... so I don't think it will let me do that?

I read somewhere that TRUNCATE-ing the table would let me make a change more easily, but I couldn't find anything saying how. I would happily


SELECT * INTO MyTempTable FROM MyRealTable

then TRUNCATE the table, make the change, and then copy that data back ... but I can't see how to use TRUNCATE'd state of the table to make the job easier.

Otherwise it (loss of ordinal position) would definitely be an acceptable solution, because much less testing required than the Drop/Re-create scenario which involves:

Script the change - using SSMS that is easy

However ...

SSMS does not output the COLLATION on the VARCHAR columns (because on the DEV database they are Database-Default, but that's potentially not the case on Target databases).

SSMS adds in a number of COMMIT/BEGIN TRANS statements - in particular where it decides it feels the need to add a TABLE LOCK directive. This means that the script cannot safely be run, such that it can be ROLLBACK'd, without hand editing.

SSMS generates DROP/CREATE statements for Constraints of various types. Our DEV database includes all possible related objects, so this would be the maximal-set. However, the target databases may not include all such related objects, and both the DROP and CREATE would thus fail. Therefore the scripts must be hand edited to add IF EXISTS (xxx) to the DROP and corresponding code for condition-CREATE.

We DO explicitly & consistently name all such objects, however without this we might have a situation where different databases had different names - and thus the DROP would be somewhat fraught!

Similarly the SSMS script does not include a "If more, unexpected, FKeys exist then bail-out". So if we have added (accidentally or deliberately) an additional FKey - perhaps to a client-specific table - then the script would fail.

Probably some other things that I've overlooked

... all of which ... means that I cannot fix this without a full QA cycle - hugely costly and time consuming

Sorry .. feeling a) a bit of a prat at having made a novice mistake and b) hugely frustrated at the "cost" of the fix.

and also because I think the reason for the error is because the DESIGN tab's properties for IDENTITY are in the opposite order to the IDENTITY() function in the script for the CREATE and thus I followed-my-nose and got one, or other, the wrong way round.

Stable-door now closed for future releases because of the checking script I posted earlier. Cold-comfort though ...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-17 : 17:37:27

I tried to do the following on my SQL 2008 R2 and it seemed to work.

1. In SSMS Tools -> Options -> Designers -> Table And Database Designers, unchecked the "Prevent saving changes that require table re-creation".

2. Ran this script to create two tables:


CREATE TABLE dbo.TestIdentity(id INT IDENTITY(1,10) NOT NULL PRIMARY KEY CLUSTERED,id2 INT);
INSERT INTO dbo.TEstIdentity VALUES (1),(2);

SELECT * FROM TestIdentity;

CREATE TABLE dbo.TestId2(id INT REFERENCES TestIdentity(id),id3 INT);

INSERT INTO TestId2 VALUES(11,2);

3. In the table designer (from Object Explorer right click and Design) Changed the Identity Increment to 5 and saved.

I assume that under the covers it is dropping and recreating the table, but it being the PK and the presence of the primary key didn't seem to cause any problems. Not sure if you have tried it and does not fit your situation, but thought I will post it anyway.

Kristen
Test

22859 Posts

Posted - 2011-08-17 : 18:18:05

Thanks. I'm sure that would work. But we need to build a release patch - based on the script that SSMS will have produced when you pressed SAVE in step (3) - you can see the script for what SSMS will do behind-the-scenes when you press SAVE by using the "Table designer" : "Generate change script" menu option.

We can then run that script on QA and STAGING before then running it on PRODUCTION, and be sure it will operate identically each time (compared to doing it manually in SSMS and maybe pressing something different, or forgetting a step ...

)

That raises the issues I described above which need testing to make sure the script will work flawlessly on the target database (in our case there are multiple target databases that are liable to have slightly different constraints on the tables in question)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-17 : 18:35:58

1. Are there any foreign key reference to this column?
2. What are you doing to do with the already added values that started below 10000

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-17 : 18:44:59

Ok disregard question 2 :(

I would go with a migration script

Drop any foreign key references to problematic column (you can find these by viewing dependencies of table Right Click on table)
DROP PRIMARY KEY CONSTRAINT/INDEXES etc on problematic column
Rename mycolumn to mycolumn_old
Add a new column mycolumn column with identity(10000,1)
set insert_identity on
UPDATE TABLE
SET mycolumn = mycolumn_old

set insert_identity off
PRIMARY KEY CONSTRAINT/INDEXES/foreign key references etc
leave mycolumn_old or delete it I would leave it

If you don't have the passion to help people, you have no passion

Kristen
Test

22859 Posts

Posted - 2011-08-17 : 18:47:10

1. Yes

2. Live with them

... and the ones above 10000

We have come clients not-yet-upgraded, so they will be 10,000-based. For the others I will put some DBCC CHECKIDENT code in my script to set the Seed to the current MAX(ID) (after changing the Increment to One). Here's the code I'm using:


DECLARE @intIdentity int
SELECT @intIdentity = COALESCE(MAX(My_ID), 10000) FROM dbo.MyTable
SELECT [MAX My_ID]=@intIdentity
SELECT @intIdentity = CASE WHEN @intIdentity < 10000 THEN 10000 ELSE @intIdentity END
SELECT [NEW My_ID]=@intIdentity
DBCC CHECKIDENT ('dbo.MyTable', RESEED, @intIdentity)
GO

Kristen
Test

22859 Posts

Posted - 2011-08-17 : 18:48:46

"Add a new column mycolumn column with identity(10000,1)"

Interesting. Will that work given that mycolumn_old already has IDENTITY property?

I'll give it a go ...

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-17 : 19:01:55

well you will have to first demote the other one :) from being identity column

If you don't have the passion to help people, you have no passion

Kristen
Test

22859 Posts

Posted - 2011-08-17 : 19:05:21

[code]

EXEC sp_rename 'MyTable.T_ID', 'T_OLD_ID', 'COLUMN'
GO

ALTER TABLE dbo.MyTable ADD
T_ID int IDENTITY(10000, 1) NOT NULL
GO
[/code]
gives me:

"Multiple identity columns specified for table 'MyTable'. Only one identity column per table is allowed."

Here's my script:
[code]
PRINT 'Ignore error if table does not exist'
PRINT '-----------'
DROP TABLE MyTable
PRINT '-----------'
GO
CREATE TABLE MyTable
(
T_ID int IDENTITY(1, 10000) NOT NULL,
T_Col2 int,
T_Col3 int,
T_Col4 int,
T_Col5 int
)

ALTER TABLE dbo.MyTable ADD CONSTRAINT
PK_MyTable PRIMARY KEY CLUSTERED
(
T_ID
)

INSERT INTO MyTable
(
T_Col2, T_Col3, T_Col4, T_Col5
)
SELECT 2, 3, 4, 5 UNION ALL
SELECT 2, 3, 4, 5 UNION ALL
SELECT 2, 3, 4, 5 UNION ALL
SELECT 2, 3, 4, 5 UNION ALL
SELECT 2, 3, 4, 5 UNION ALL
SELECT 2, 3, 4, 5 UNION ALL
SELECT 2, 3, 4, 5

SELECT *
FROM MyTable
GO

PRINT 'Drop the PK'
ALTER TABLE dbo.MyTable
DROP CONSTRAINT PK_MyTable
PRINT '-----------'
GO

PRINT 'Rename T_ID to T_OLD_ID'
EXEC sp_rename 'MyTable.T_ID', 'T_OLD_ID', 'COLUMN'
PRINT '-----------'
GO

PRINT 'Alter Column (to remove IDENTITY property)'
ALTER TABLE dbo.MyTable ALTER COLUMN
T_OLD_ID int NOT NULL -- Try to remove IDENTITY property
PRINT '-----------'
GO

PRINT 'Add Column T_ID (with IDENTITY property)'
ALTER TABLE dbo.MyTable ADD
T_ID int IDENTITY(10000, 1) NOT NULL
PRINT '-----------'
GO
[/code]

Kristen
Test

22859 Posts

Posted - 2011-08-17 : 19:05:58

"well you will have to first demote the other one :) from being identity column"

Demote? Can you clarify that please? Thanks.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-17 : 19:06:12

oop! Ok please check this script out

sp_configure 'allow update', 1
go
reconfigure with override
go

update syscolumns set colstat = 0 --turn off bit 1 which indicates identity column
where id = object_id('table_name') and name = 'column_name'
go

exec sp_configure 'allow update', 0
go
reconfigure with override
go

If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-17 : 19:08:20

another option which is something you did not want is

drop all foreign key references to this columns
dump all data to a secondary migration table
recreate table with proper identity

set insert identity on
insert from migration table
set insert identity off

question: how many tables reference problematic table and also how many rows total?

If you don't have the passion to help people, you have no passion

Kristen
Test

22859 Posts

Posted - 2011-08-17 : 19:10:40

"Server: Msg 259, Level 16, State 1, Line 4
Ad hoc updates to system catalogs are not allowed."

I think that came in with SQL 2005 ?

It probably would have been fine in SQL 2000 !

Or I've screwed up somewhere?

Kristen
Test

22859 Posts

Posted - 2011-08-17 : 19:17:14

Yes:

"drop all foreign key references to this columns
dump all data to a secondary migration table
recreate table with proper identity

set insert identity on
insert from migration table
set insert identity off"

will definitely work. But as described before we have databases with variable numbers/names of FKeys so its not entirely plain-sailing, and because the change then becomes non trivial (including "risk" associated with other modifications we will have to make to the standard SSMS-generated change-script), we will need to do full QA checks to make sure nothing gets broken in the rollout process. Doing that on the staging databases (copy production database backup files to suitable machine, databases are huge, that will mean scavenging disk space etc., restore, test it ... maybe repeat ...) will take a long time ... then we have to make a make a backout process in case it still kills the production database when we run it, and then schedule downtime to do the upgrade on the Production databases ...

I think I'm hosed on this one ...

Kristen
Test

22859 Posts

Posted - 2011-08-17 : 19:22:34

"question: how many tables reference problematic table and also how many rows total?"

There are three tables that got mucked up like this.

There are about 20 Fkeys. That's not a problem, easily scripted (although we have to make sure we have conditionally scripted all possible FKeys for all databases (one per client).

There are not many rows (yet!)

Half a dozen client databases have this issue in production. They are in use 24/7.

On my DEV server it only takes a minute or so to run the script on a small database.

I'm comfortable that will work.

However, if I could have found a way to change the column in-situ that would save a lot of Staging database rehearsals.

But I reckon I'm fresh-out-of-luck ...

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-17 : 19:23:08

don't give up yet!! ok in dev do the following

"drop all foreign key references to this columns
dump all data to a secondary migration table
recreate table with proper identity

set insert identity on
insert from migration table
set insert identity off"

Use Red Gate migration tool (free for 14 days ) to compare dev and sqa. DO NOT RUN script just save it and see what you can glean. :(

If you don't have the passion to help people, you have no passion

Kristen
Test

22859 Posts

Posted - 2011-08-17 : 19:34:24

Its a nice idea. I'll take a bet that it will give me a script the same as the steps you've described !

In case anyone has RedGate available here's my script (ignoring the FKey issue) to convert my earlier MyTable to the corrected IDENTITY - which could then be compared to make a change-script


CREATE TABLE dbo.Tmp_MyTable
	(
	T_ID int NOT NULL IDENTITY (10000, 1),
	T_Col2 int NULL,
	T_Col3 int NULL,
	T_Col4 int NULL,
	T_Col5 int NULL
	)
GO

SET IDENTITY_INSERT dbo.Tmp_MyTable ON
GO
IF EXISTS(SELECT * FROM dbo.MyTable)
	 EXEC('INSERT INTO dbo.Tmp_MyTable (T_ID, T_Col2, T_Col3, T_Col4, T_Col5)
		SELECT T_ID, T_Col2, T_Col3, T_Col4, T_Col5 FROM dbo.MyTable WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_MyTable OFF
GO
DROP TABLE dbo.MyTable
GO
EXECUTE sp_rename N'dbo.Tmp_MyTable', N'MyTable', 'OBJECT' 
GO
ALTER TABLE dbo.MyTable ADD CONSTRAINT
	PK_MyTable PRIMARY KEY CLUSTERED 
	(
	T_ID
	)
GO

Kristen
Test

22859 Posts

Posted - 2011-08-17 : 19:35:54

Or compare two instances of the table - one with IDENTITY(1, 10000), and the other created with IDENTITY(10000, 1)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-08-17 : 23:36:27

can't you install Reg Gate yourself and try it.

If you don't have the passion to help people, you have no passion

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources