IDENTITY column reseed

Hi,
I am using a SQL server table in my Java application. I want to prevent
reseeding of the identity column.
Table T1:
C1 INT IDENTITY NOT FOR REPLICATION PRIMARY KEY,
C2 VARCHAR(100)
Say I have values:
1, 'abc'
2, 'xyz'
I can explicitly insert a value for C1 into this table by :
SET IDENTITY_INSERT T1 ON
INSERT INTO T1 VALUES(101, 'foo');
Now, if I do,
INSERT INTO T1 VALUES('blah');
I see the following data in the table:
1, 'abc'
2, 'xyz'
101, 'foo'
102, 'blah'
I have specified identity column C1 as NOT FOR REPLICATION and inserted
'foo' with explicit C1 value of 101.
Why does the identity column reseed after this?
How do I achieve a key value of 3 for the row with 'blah'?
Basically I expec to see:
1, 'abc'
2, 'xyz'
101, 'foo'
3, 'blah'
Any suggestions appreciated.
Thanks,
Esak.
I am curious as to why you want to do this. If you reseed you will run into
problems when your identity value starts to hit 100.
To reseed issue a DBCC CheckIdent('T1',reseed,3)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Esak" <esankaran@.datamirror.com> wrote in message
news:exYX6fC3FHA.3136@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am using a SQL server table in my Java application. I want to prevent
> reseeding of the identity column.
> Table T1:
> C1 INT IDENTITY NOT FOR REPLICATION PRIMARY KEY,
> C2 VARCHAR(100)
> Say I have values:
> 1, 'abc'
> 2, 'xyz'
> I can explicitly insert a value for C1 into this table by :
> SET IDENTITY_INSERT T1 ON
> INSERT INTO T1 VALUES(101, 'foo');
> Now, if I do,
> INSERT INTO T1 VALUES('blah');
> I see the following data in the table:
> 1, 'abc'
> 2, 'xyz'
> 101, 'foo'
> 102, 'blah'
> I have specified identity column C1 as NOT FOR REPLICATION and inserted
> 'foo' with explicit C1 value of 101.
> Why does the identity column reseed after this?
> How do I achieve a key value of 3 for the row with 'blah'?
> Basically I expec to see:
> 1, 'abc'
> 2, 'xyz'
> 101, 'foo'
> 3, 'blah'
>
> Any suggestions appreciated.
> Thanks,
> Esak.
>
>
|||Hi Hilary,
I am looking for a way to turn off reseeding the IDENTITY column value.
The SQL server replication agent looks for the NOT FOR REPLICATION option on
IDENTITY columns and does not reseed the value. How can I achieve the same
in my SQL/Java application?
Do you think the NOT FOR REPLICATION option for IDENTITY column is
applicable only for the replication agent?
Thanks,
Esak.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:Ormz5dK3FHA.3188@.TK2MSFTNGP12.phx.gbl...
> I am curious as to why you want to do this. If you reseed you will run
into
> problems when your identity value starts to hit 100.
> To reseed issue a DBCC CheckIdent('T1',reseed,3)
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Esak" <esankaran@.datamirror.com> wrote in message
> news:exYX6fC3FHA.3136@.TK2MSFTNGP09.phx.gbl...
>