Showing posts with label java. Show all posts
Showing posts with label java. Show all posts

Friday, March 9, 2012

Identity combined with 'other' method.

We use two different development methods :
1.
Classical, where we want to use identity as present in SQL-server.
2.
Java, where they do everything their own way, including identity.
The have a special table to fetch 20 numbers at a time and
use that as identity in another table.
Now both methods have to be combined. One of the ideas was
odd and even numbers. One method used odd numbers the other uses
the even numbers. When inserting with Java switch identity
insert on, do the insert, switch the identity insert of.
I do not like this 'solution', but I would like some SOLID arguments
against it.
Arguments against this method ?
Solutions to this problem ?
thanks in advance,
(If a similar question appears on the list,
I did send this question before, but I do not see it.
probably clicked it away.).
ben brugmanben,
If I am generating IDs outside of SQL for some reason, I would incline to
creating code inside SQL to generate the IDs algorithmically. However, it
is not impossible, just messy.
(You might want to read up on Identities and Replication in the BOL for some
thoughts.)
You would need to be careful in running DBCC CHECKIDENT (if you ever do) not
to mess up your interleaving.
If you decide to do even/odd, then Seed the identity with an even number and
an increment of 2. Should reseeding ever become a problem you will need to
get the highest identity and specifically reseed to the next even number.
A safer method would be to seed the SQL Server identities high (e.g.
1,000,000,000) and let Java play in the lower range. That way, should you
ever need to reseed because of some problem there is less to fool with.
(And the guy who comes after you is less likely to make a mistake.)
FWIW
Russell Fields
"ben brugman" <ben@.niethier.nl> wrote in message
news:%23CknUgegDHA.2084@.TK2MSFTNGP10.phx.gbl...
> We use two different development methods :
> 1.
> Classical, where we want to use identity as present in SQL-server.
> 2.
> Java, where they do everything their own way, including identity.
> The have a special table to fetch 20 numbers at a time and
> use that as identity in another table.
> Now both methods have to be combined. One of the ideas was
> odd and even numbers. One method used odd numbers the other uses
> the even numbers. When inserting with Java switch identity
> insert on, do the insert, switch the identity insert of.
> I do not like this 'solution', but I would like some SOLID arguments
> against it.
> Arguments against this method ?
> Solutions to this problem ?
> thanks in advance,
> (If a similar question appears on the list,
> I did send this question before, but I do not see it.
> probably clicked it away.).
> ben brugman
>

Wednesday, March 7, 2012

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