"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.
>
>
>

"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...
> > 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.
> >
> >
> >
>
>