primary key generation [Archive]

View Full Version : primary key generation

cfsavant

02-07-02, 20:26

The application I am writing must run on either SQL Server or Oracle.
I want to know what the best approach is for generation of unique id's/primary key values in this case. One that is reliable and will scale.

Currently I am generating my own 30 length varchar uuid.

The problem I have with surrogate keys is that SQL Server does not migrate them very well and oracle requires different sql statement for sequences. I want to avoid writing db specific queries.

I have been restricted from using stored procs or minimizing their use for migration and installation purposes. I think that if I have a valid enough argument, I could use a store proc to handle this.

Any suggestions are appreciated.

Second question:
Will 30 varchar length field as a primary key cause performance problems on large tables?

Thanks in advance.

vududoc

02-08-02, 14:17

why don't you build your primary keys using multiple columms of your tables, such as:
1) <lastName> + <firstName> + <midInit> + <ssNumb>
2) <compName> + <div> + <office> + <state> + <city> + <accntNo>

MattR

02-08-02, 14:23

Here is a Sybase document illustrating different primary key generation approaches.
http://www.wallaceis.com/resource/article/Sybase/Surrogate%20Primary%20Key%20Generation.htm

Sybase T-SQL and MS SQL T-SQL are very, very similar so it should provide some use.

cfsavant

02-09-02, 13:38

thanks, that was an excellent resource.