MS SQL Server - Turn off Reseed for IDENTITY

Author

Turn off Reseed for IDENTITY

Esak

2005-10-29, 3:23 am

Hi,
I am using a SQL server table in my Java application. I want to prevent
reseeding of the identity column. Here is my scenario:

Table T1:
C1 INT IDENTITY NOT FOR REPLICATION PRIMARY KEY,
C2 VARCHAR(100)

Say, I have the following data in T1:
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.

Hari Prasad

2005-10-29, 7:23 am

Hi,

Once you set a higher value automatically the next number will be increment
to old number.
To ressed to 3 you can use CHECKIDENT command. See the command DBCC
CHECKIDENT in books online.

Thanks
Hari

"Esak" < esankaran@datamirror
.com> wrote in message
news:%23cwUUhC3FHA.700@TK2MSFTNGP15.phx.gbl...
> Hi,
> I am using a SQL server table in my Java application. I want to prevent
> reseeding of the identity column. Here is my scenario:
>
> Table T1:
> C1 INT IDENTITY NOT FOR REPLICATION PRIMARY KEY,
> C2 VARCHAR(100)
>
> Say, I have the following data in T1:
> 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.
>
>
>

Esak

2005-10-31, 3:23 am

Hi,

Is it possible to prevent reseed of the IDENTITY column?

The SQL Server replication agent looks at the NOT FOR REPLICATION option and
does not reseed the IDENTITY value during inserts. Is this possible to
achieve the same effect in a SQL/Java application?

Any idea what is the complete meaning of 'NOT FOR REPLICATION' option? Is
this option used only by the SQL server replication agent?

Thanks,
Esak.

"Hari Prasad" < hari_prasad_k@hotmai
l.com> wrote in message
news:emGxdwG3FHA.3276@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> Once you set a higher value automatically the next number will be
increment
> to old number.
> To ressed to 3 you can use CHECKIDENT command. See the command DBCC
> CHECKIDENT in books online.
>
> Thanks
> Hari
>
> "Esak" < esankaran@datamirror
.com> wrote in message
> news:%23cwUUhC3FHA.700@TK2MSFTNGP15.phx.gbl...
>
>

Sponsored Links