Choice of Primary key datatype

[spaliwal]

Hi,

We are in development phase of an Internet application of around 200 tables and 1.5 - 2 GB of expected data volume with around 200 net users at a time.
Database server is sql server 2000 , we are using OID varchar(32) as a non business key primary key to every table , and this OID is used as a FK whereever Parent-Child relationsship exists.
OID is a system wide unique number we are generating it from a seed table SequenceOID which contains a single row of last no. used for OID. From a stored procedure we are accessing the no. from SequenceOID table and in the same Stored procedure we are updating the table field value to next incremented no.

I am in doubt will it work when system is in Production , since everywhere in the system same procedure is calling for OID generation.

 

[fluteplr]

If you are really generating a number why are you storing in in a varchar(32)? Store it in a int or if you expect your tables to get really huge in a bigint. Even a big int field is only 8 bytes which is 4 times smaller than the field you are using. Also why do you need a system wide unique number, generally a number that is unique for that table is quite sufficient? I use a similar stored proc. technique but I pass in the table name and keep a counter for each table. I also only use this on tables where I need to know the key ahead of time because I will also be populating a child table at the same time, on those tables where this is not the case I just use a normal system incrementing field.

 

[spaliwal]

Hi fluteplr,

Thanks a lot ! actually its too late now to change datatype to int datatype , Yes If no.is not system wide unique then we can use your way of doing it will works fine , but still if we need system wide unique no.what is the best way you can suggest.Uniqueidentifier and newid() is one of the way which works , but it needs all the table PK-FK datatypes change to uniqueidentifier , also GUID is not easy to remember or ease of use.
We also dont know how to handle uniqueidentifier in java , if you are anybody is having any idea pl. suggest. Pl. suggest for any alternate method also.

Thanks,

Shailesh