John
I work on systems where some idiot used a decimal(9,0) as a PK and mixed
it with other tables with integers, I think with any PK the main thing
is use the same otherwise you force the server to use hash joins (v
slow) and secondly use the smallest key you can (I'm currently using a
bigint derived from the server time) I really can see no reason for
using anything larger than a bigint for a surrogate, you can use a
generator (like I do) or identity for a client high/low, if you are
going to cluster the pk you want it to be sequential
MSSql 2008 offers a 'sequential guid' but that is 16 bytes versus 8 for
a bigint
Neven
Dealing with index/ID field that is set as binary - here is the
solution that worked in case any one else comes across same....
Note for MSSQL using such a field in a JOIN SQL statement would be
perfectly fine as normal, as MSSQL has a native binary type. Delphi
ADO however does not, so this is the hoops I had to jump through when
its two different datasets and I needed to get the value in order to
open the other with SQL or as a parameter....
example - using a Company_ID from Comp table to open Contacts that
belong to that Company...
Comments from friends who know MSSQL - their opinion is that an
ID/index field that is binary is unusual practice - restricts what
kind to indexes can be made for instance. Anyone here have opinion on
this? have others come across ID/Index that is Binary often?
Step 1:
ADOQueryComp.SQL.Text:='select *, Cast(Company_ID as VARCHAR(10)) as
ACompID, CAST(Company_ID as INT) as DCOMPID from Company '+
'where Company_Name like '+likestr;
Note - Casting binary to string/Varchar does not work, but to integer
does. Go figure!
Step 2:
iCompanyID:=cdsComp.FieldbyName('DCompID').asinteger;
ADOQueryContact.SQL.Text:='select * from Contact '+
'where Company_ID = CAST('+inttostr(iCompanyID)+' AS BINARY(8))';
John
_______________________________________________
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject:
unsubscribe
_______________________________________________
NZ Borland Developers Group - Delphi mailing list
Post: delphi@delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to delphi-requ...@delphi.org.nz with Subject:
unsubscribe
