Click here to Skip to main content
15,887,302 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Using sql server 2012, I have a database with more than 100 tables and 91 of them using identity columns as primary key. I have automated the setting up of synchronization with microsoft sync framework in the desktop application uppon request by a client.

To avoid conflicts, I need to adjust the seed and increment values of all the 91 tables' identity columns during the sync setup on both server and client databases. That is to say, sql script should iterate through all the 91 tables adjusting the seed and increment values. This script will be implemented as a procedure that will be executed during provisioning of the databases.

Sync works fine, my only challenge is the adjusting of the identity columns to avoid conflicts using
IDENTITY(m,n).

Using GUID is NOT an option.

Thank you all in advance

What I have tried:

So far have been able to generate a script to get all the tables with identity columns with their respective seed, increment and last inserted values with below sql query;

SQL
DECLARE @NewSeedValue int

SET @NewSeedValue = 5

SELECT 
	OBJECT_SCHEMA_NAME(tables.object_id, db_id())
	AS SchemaName,
	tables.name As TableName,
	--'DBCC CHECKIDENT(''' + tables.name + ''', RESEED,' + CONVERT(VARCHAR,@NewSeedValue) + ')' AS [Script],
	identity_columns.name as ColumnName,
	identity_columns.seed_value,
	identity_columns.increment_value,
	identity_columns.last_value
FROM sys.tables tables 
	JOIN sys.identity_columns identity_columns 
ON tables.object_id=identity_columns.object_id
ORDER BY tables.name asc,identity_columns.name asc;
Posted
Updated 15-Apr-23 8:59am
Comments
0x01AA 15-Apr-23 9:46am    
Are Sequences mayba an option?

You can turn automatic identity off when you need to using
SQL
SET IDENTITY_INSERT sometableWithIdentity ON
And
SQL
SET IDENTITY_INSERT sometableWithIdentity OFF

And change the seed values using DBCC CHECKIDENT (Transact-SQL) - SQL Server | Microsoft Learn[^]
But ... If you need to do this more than once in the life of the DB, it's a sure sign that your DB design is wrong, and you really, really should be using GUIDs instead.

You are going to have to be really, really careful here: if multiple users try to do this at the same time, you are almost bound to get corrupted data at some point.

Would I do it? Nope. I'd redesign my DB to make my whole life a load easier later ...
 
Share this answer
 
Comments
Member 10596515 15-Apr-23 12:24pm    
Thank you for the quick response.
Fortunately this will be done once. What may trigger a reconfiguration is when another client or sale point must be added. It's a windows point of sale software that we will like to centralize the database for multiple sale points(clients) scattered across a region. Due to our unreliable internet connectivity, clients should be able to offline and sync when internet is restored.
If you don't want to use GUID for performance reasons, you might be interested in NEWSEQUENTIALID.
See: SQL SERVER - Using NEWID vs NEWSEQUENTIALID for Performance - SQL Authority with Pinal Dave[^]
 
Share this answer
 
Comments
Member 10596515 18-Apr-23 5:39am    
Thanks Rick. I will revert after examining the article.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900