[DUG] MSSQL ID field binary(8)

[Stephen Barker]

words[0]+' '+words[22]+' '+words[26]+' '+words[32]+' '+words[28]+'
'+words[-1]
 
Steve


  _____  

From: John Bird [mailto:johnkbird at paradise.net.nz] 
Sent: Tuesday, 17 August 2010 3:55 p.m.
To: NZ Borland Developers Group - Delphi List
Subject: Re: [DUG] MSSQL ID field binary(8)


I think I have to agree with your words[5] and words[6] .   Where -
words:array[0..xxx] of string;
 
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 at delphi.org.nz <mailto:delphi at delphi.org.nz> 

Admin: http://delphi.org.nz/mailman/listinfo/delphi
<http://delphi.org.nz/mailman/listinfo/delphi> 

Unsubscribe: send an email to delphi-request at delphi.org.nz
<mailto:delphi-request at delphi.org.nz>  with Subject: unsubscribe




  _____  




_______________________________________________
NZ Borland Developers Group - Delphi mailing list
Post: delphi at delphi.org.nz
Admin: http://delphi.org.nz/mailman/listinfo/delphi
Unsubscribe: send an email to delphi-request at delphi.org.nz with Subject:
unsubscribe 

No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 9.0.851 / Virus Database: 271.1.1/3061 - Release Date: 08/16/10
18:35:00




-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://listserver.123.net.nz/pipermail/delphi/attachments/20100817/5275f8af/attachment-0001.html