May 14, 2013 at 9:29 am
Hi!
Can I insert a table without identity reseed?
Exapmle:
CREATE TABLE #table (id int IDENTITY (1,1), col1 int)
INSERT INTO #table DEFAULT VALUES
-- Id = 1
-- If I insert identity values, then identity is reseed
SET IDENTITY_INSERT #table ON
INSERT INTO #table ( id, col1 )VALUES ( 1000, 0)
SET IDENTITY_INSERT #table OFF
-- Id = 1000
INSERT INTO #table DEFAULT VALUES
-- Id = 1001 and this is my problem.
SELECT * FROM #table
Thanks
May 14, 2013 at 9:35 am
salliven (5/14/2013)
Hi!Can I insert a table without identity reseed?
Exapmle:
CREATE TABLE #table (id int IDENTITY (1,1), col1 int)INSERT INTO #table DEFAULT VALUES
-- Id = 1
-- If I insert identity values, then identity is reseed
SET IDENTITY_INSERT #table ON
INSERT INTO #table ( id, col1 )VALUES ( 1000, 0)
SET IDENTITY_INSERT #table OFF
-- Id = 1000
INSERT INTO #table DEFAULT VALUES
-- Id = 1001 and this is my problem.
SELECT * FROM #table
Thanks
I cannot see where you have found the problem?
After inserting 1000 in identity column, what kind of value you would expect for the subsequent insert?
It cannot start from 1, otherwise it may end up with generating duplicate identity value which is nonsense as it's identity!
May 14, 2013 at 9:38 am
I absolutely agree with Eugene that this is not a good idea but you can work with by using your own reseed.
CREATE TABLE #table (id int IDENTITY (1,1), col1 int)
INSERT INTO #table DEFAULT VALUES
-- Id = 1
declare @SeedValue int
select @SeedValue = IDENT_CURRENT('#table')
-- If I insert identity values, then identity is reseed
SET IDENTITY_INSERT #table ON
INSERT INTO #table ( id, col1 )VALUES ( 1000, 0)
SET IDENTITY_INSERT #table OFF
-- Id = 1000
DBCC CHECKIDENT ('#table', RESEED, @SeedValue)
INSERT INTO #table DEFAULT VALUES
-- Id = 1001 and this is my problem.
SELECT * FROM #table
drop table #table
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 14, 2013 at 11:21 am
Thanks for replay!
After inserting 1000 in identity column, what kind of value you would expect for the subsequent insert?
id = 2
It cannot start from 1, otherwise it may end up with generating duplicate identity value which is nonsense as it's identity!
I'will manage identity range manually.
@sean Lange:
DBCC CHECKIDENT ('#table', RESEED, @SeedValue)
CHECKIDENT isn't good. I would like insert without identity reseed.
May 14, 2013 at 11:36 am
The only way for SQL Server to start counting from 2 after inserting 1000 into identity is to reseed it. But, it's a stupid thing to do anyway.
However, if you are going to maintain range manually, why are you using identity at all?
May 14, 2013 at 11:55 am
It is a long story.
I have two SQL servers and databases (same databases are on two servers).
These databases are "multi master" (similar as peer-to-peer or merge) replication with a program of company (This isn't the Microsoft replication).
This program isn't know insert without identity reseed.
May 14, 2013 at 12:25 pm
salliven (5/14/2013)
@sean Lange:
DBCC CHECKIDENT ('#table', RESEED, @SeedValue)
CHECKIDENT isn't good. I would like insert without identity reseed.
That is how it works salliven. When you manually insert values into an identity the sql engine will set the next value to be whatever value you inserted plus the increment. What you are doing goes against the point of using an identity. If you are going to manually manage the values then you should manually manage the values all the time, not just when it suits you.
The only way you are going to accomplish this while using an identity is to reseed it after you insert your own values.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 14, 2013 at 12:47 pm
Ok, thanks!
But the Microsoft replication is knows insert without identity reseed.
May 14, 2013 at 12:51 pm
salliven (5/14/2013)
Ok, thanks!But the Microsoft replication is knows insert without identity reseed.
How do you know that? I am not disputing it, I am curious if you know that as fact or speculation. Of course you also said that you are not using replication so I am not sure what that do with your situation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 14, 2013 at 1:22 pm
How do you know that?
I'm managing more Microsoft replication.
But Microsoft replication is slow and inflexible. Our replication program is fast and flexible (example: maximum 0,5 sec replication latency, more smaller disk overhead, replication between mssql and mysql, etc), but insert without identity reseed is a big problem.
May 14, 2013 at 1:28 pm
salliven (5/14/2013)
How do you know that?
I'm managing more Microsoft replication.
But Microsoft replication is slow and inflexible. Our replication program is fast and flexible (example: maximum 0,5 sec replication latency, more smaller disk overhead, replication between mssql and mysql, etc), but insert without identity reseed is a big problem.
I don't see how that means that MS replication does not reseed identities. I have never dug deep into the inner workings of replication but I would not be surprised if the don't worry about it like this. The point is to keep the values consistent across databases which would include identity values. I would expect they do some reseeding as required. Not really sure why doing something along the lines of what I suggested won't work but I don't really know what you are working on either.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 14, 2013 at 1:43 pm
Exapmle: we're monitoring MS replication with profiler, but it don't use identity reseed. It use always simple insert.
May 14, 2013 at 1:57 pm
salliven (5/14/2013)
Exapmle: we're monitoring MS replication with profiler, but it don't use identity reseed. It use always simple insert.
Profiler may not be capturing everything as you think. If the reseed logic is in a stored proc you won't see it. There may also be some logic that you can't see here because it is in the binaries.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 2, 2016 at 4:20 pm
I am faced with a similar problem . I have a Peer-to-Peer replication which is not performing optimally because of BLOB data , so I have come up with customized replication wherein each server has a partitioned Identity insert range eg ( 1st server indentity range from 1 - 300 million ) and my second server the identity range starts from 300 million + . I am sure that the inserts will not cross over the range in each server.
I am using custom stored procedures to replicate data. When replicating data from the second server to the first , I do not want to reseed the identity column because of the insert . Microsoft replication is able to do so with the "NOT FOR REPLICATION" and bypass the reseed of the identity columns. How do I implement similar functionality for my custom stored procs. I tried to profile capture the sp's called but did not find anything.
It is not possible for me store and then reseed the identity on the column after every insert because the system is heavily concurrently used and may result in a primary key violation if the reseeding is not proper.
How is Microsoft able to bypass the reseeding with the "NOT FOR REPLICATION". I was not able to find any documentation online. Please help.
May 2, 2016 at 4:36 pm
salliven (5/14/2013)
Hi!Can I insert a table without identity reseed?
Exapmle:
CREATE TABLE #table (id int IDENTITY (1,1), col1 int)INSERT INTO #table DEFAULT VALUES
-- Id = 1
-- If I insert identity values, then identity is reseed
SET IDENTITY_INSERT #table ON
INSERT INTO #table ( id, col1 )VALUES ( 1000, 0)
SET IDENTITY_INSERT #table OFF
-- Id = 1000
INSERT INTO #table DEFAULT VALUES
-- Id = 1001 and this is my problem.
SELECT * FROM #table
Thanks
Will it help?
CREATE TABLE #table (id int IDENTITY (-1,-1), col1 int)
INSERT INTO #table DEFAULT VALUES
-- Id = -1
-- If I insert identity values, then identity is reseed - only if the new value is on the way of auto-incrementing ID's
SET IDENTITY_INSERT #table ON
INSERT INTO #table ( id, col1 )VALUES ( 1000, 0)
SET IDENTITY_INSERT #table OFF
-- Id = 1000
INSERT INTO #table DEFAULT VALUES
-- Id = -2 - problem's solved.
SELECT * FROM #table
DBCC CHECKIDENT (#table)
-- Checking identity information: current identity value '-2', current column value '-2'.
Separate your 2 identity space by putting one of them into negative area.
Then they never gonna interfere, and your negative auto-increment is not gonna be affected by positive replicated ID's.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply