Subject RE:[IBO] Char(20) vs Int64 for Primary Key?
Author Alan McDonald
> I posted this in the FB group... but a response made me think that
> maybe this forum is a better place as I am using the IBO TDataset
> objects with Delphi to access my FB 1.5 database...
> We are preparing to convert a MS SQLServer7 database and have been
> desiging all the tables with PrimaryKeys of Char(20) - generated by
> our own routines, a bit like our own GUID...
> Most of our tables are moderate size with a few tables around 500,000
> to 1 million records.
> Most of our queries appear to be fast... but now we are worried we
> should have used an BIGINT (Int64) instead of our Char(20) for our
> PK's...
> I believe Ann once stated that a BIGINT and a Char(8) would be
> equivelant in the time it takes to compare values... so then a Char
> (20) would be 2.5 times longer... if my math isn't too rusty.
> What are most people using for PK's?
> and can you _please_ offer your suggestions because we really want to
> hear something that will either push us one way or make us feel like
> we are sitting OK...
> Many thanks,
> --Raymond

IMO you have to have a very good reason to generate PK values at the client
and not have the server generate them for you. There's no way to doing this
using integer values that I know of.
It is truly a performance penalty to use GUID like PK values even those who
continue to use them concede this point. But they continue for the very good
reasons they have.
Having the server generate PK values and having these values as integer is a
desired method of achieving record uniqueness. Indexes are sound and fast. I
always vote for integer server PKs.
Some people have chosen a replication model which requires that the client
create PKs so UUIDs are a choice for this instance.