<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
John<br>
<br>
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<br>
<br>
MSSql 2008 offers a 'sequential guid' but that is 16 bytes versus 8
for a bigint<br>
<br>
Neven<br>
<blockquote cite="mid:DCC48DE031DC43EFB006AEFA1AEA68AB@JohnSony2"
type="cite">
<title>Message</title>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<meta name="GENERATOR" content="MSHTML 8.00.7600.16588">
<style>@font-face {
font-family: Calibri;
}
@font-face {
font-family: Consolas;
}
@page WordSection1 {size: 8.5in 11.0in; margin: 1.0in 1.0in 1.0in 1.0in; }
P.MsoNormal {
MARGIN: 0in 0in 0pt; FONT-FAMILY: "Calibri","sans-serif"; FONT-SIZE: 11pt
}
LI.MsoNormal {
MARGIN: 0in 0in 0pt; FONT-FAMILY: "Calibri","sans-serif"; FONT-SIZE: 11pt
}
DIV.MsoNormal {
MARGIN: 0in 0in 0pt; FONT-FAMILY: "Calibri","sans-serif"; FONT-SIZE: 11pt
}
A:link {
COLOR: blue; TEXT-DECORATION: underline; mso-style-priority: 99
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline; mso-style-priority: 99
}
A:visited {
COLOR: purple; TEXT-DECORATION: underline; mso-style-priority: 99
}
SPAN.MsoHyperlinkFollowed {
COLOR: purple; TEXT-DECORATION: underline; mso-style-priority: 99
}
SPAN.EmailStyle17 {
FONT-FAMILY: "Calibri","sans-serif"; COLOR: windowtext; mso-style-type: personal-compose
}
..MsoChpDefault {
mso-style-type: export-only
}
DIV.WordSection1 {
page: WordSection1
}
</style>
<div>
<div><font size="2" face="Arial">Dealing with index/ID field
that is set as binary - here is the solution that worked in
case any one else comes across same....</font></div>
<div> </div>
<div><font size="2" face="Arial">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....</font></div>
<div> </div>
<div><font size="2" face="Arial">example - using a Company_ID
from Comp table to open Contacts that belong to that
Company...</font></div>
<div> </div>
<div><font size="2" face="Arial">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?</font></div>
<div> </div>
<div><font size="2" face="Arial">Step 1:</font></div>
<div> </div>
<div><font size="2" face="Arial">
ADOQueryComp.SQL.Text:='select *, Cast(Company_ID as
VARCHAR(10)) as ACompID, CAST(Company_ID as INT) as DCOMPID
from Company '+<br>
'where Company_Name like '+likestr;<br>
</font></div>
<div><font size="2" face="Arial">Note - Casting binary to
string/Varchar does not work, but to integer does. Go
figure!</font></div>
<div> </div>
<div> </div>
<div><font size="2" face="Arial">Step 2:</font></div>
<div> </div>
<div><font size="2" face="Arial">
iCompanyID:=cdsComp.FieldbyName('DCompID').asinteger;<br>
</font></div>
<div><font size="2" face="Arial">
ADOQueryContact.SQL.Text:='select * from Contact '+<br>
'where Company_ID = CAST('+inttostr(iCompanyID)+' AS
BINARY(8))';<br>
</font></div>
</div>
<div><font size="2" face="Arial">John</font></div>
<pre wrap="">
<fieldset class="mimeAttachmentHeader"></fieldset>
_______________________________________________
NZ Borland Developers Group - Delphi mailing list
Post: <a class="moz-txt-link-abbreviated" href="mailto:delphi@delphi.org.nz">delphi@delphi.org.nz</a>
Admin: <a class="moz-txt-link-freetext" href="http://delphi.org.nz/mailman/listinfo/delphi">http://delphi.org.nz/mailman/listinfo/delphi</a>
Unsubscribe: send an email to <a class="moz-txt-link-abbreviated" href="mailto:delphi-request@delphi.org.nz">delphi-request@delphi.org.nz</a> with Subject: unsubscribe</pre>
</blockquote>
<br>
</body>
</html>