<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE></TITLE>
<META name=GENERATOR content="MSHTML 8.00.6001.18928"></HEAD>
<BODY style="PADDING-LEFT: 10px; PADDING-RIGHT: 10px; PADDING-TOP: 15px"
id=MailContainerBody leftMargin=0 topMargin=0 bgColor=#ffffff text=#000000
name="Compose message area" CanvasTabStop="true">
<DIV dir=ltr align=left><SPAN class=418195210-17082010><FONT color=#800000
size=2 face=Verdana>words[0]+' '+<SPAN class=418195210-17082010><FONT
color=#800000 size=2 face=Verdana>words[22]+' '+<SPAN
class=418195210-17082010><FONT color=#800000 size=2 face=Verdana>words[26]+'
'+<SPAN class=418195210-17082010><FONT color=#800000 size=2
face=Verdana>words[32]+' '+<SPAN class=418195210-17082010><FONT color=#800000
size=2 face=Verdana>words[28]+' '+<SPAN class=418195210-17082010><FONT
color=#800000 size=2
face=Verdana>words[-1]</FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=418195210-17082010><FONT color=#800000
size=2 face=Verdana><SPAN class=418195210-17082010><FONT color=#800000 size=2
face=Verdana><SPAN class=418195210-17082010><FONT color=#800000 size=2
face=Verdana><SPAN class=418195210-17082010><FONT color=#800000 size=2
face=Verdana><SPAN class=418195210-17082010><FONT color=#800000 size=2
face=Verdana><SPAN class=418195210-17082010><FONT color=#800000 size=2
face=Verdana><SPAN
class=418195210-17082010></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=418195210-17082010><FONT color=#800000
size=2 face=Verdana><SPAN class=418195210-17082010><FONT color=#800000 size=2
face=Verdana><SPAN class=418195210-17082010><FONT color=#800000 size=2
face=Verdana><SPAN class=418195210-17082010><FONT color=#800000 size=2
face=Verdana><SPAN class=418195210-17082010><FONT color=#800000 size=2
face=Verdana><SPAN class=418195210-17082010><FONT color=#800000 size=2
face=Verdana><SPAN
class=418195210-17082010>Steve</SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></FONT></SPAN></DIV><BR>
<BLOCKQUOTE
style="BORDER-LEFT: #800000 2px solid; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px"
dir=ltr>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B> John Bird
[mailto:johnkbird@paradise.net.nz] <BR><B>Sent:</B> Tuesday, 17 August 2010
3:55 p.m.<BR><B>To:</B> NZ Borland Developers Group - Delphi
List<BR><B>Subject:</B> Re: [DUG] MSSQL ID field
binary(8)<BR></FONT><BR></DIV>
<DIV></DIV>
<DIV><FONT size=2 face=Arial>I think I have to agree with your words[5] and
words[6] . Where - words:array[0..xxx] of
string;</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV><FONT size=2 face=Arial>John</FONT></DIV>
<DIV><FONT size=2 face=Arial></FONT> </DIV>
<DIV>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></DIV>
<BLOCKQUOTE cite=mid:DCC48DE031DC43EFB006AEFA1AEA68AB@JohnSony2 type="cite">
<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>
<P>
<HR>
<P></P>_______________________________________________<BR>NZ Borland
Developers Group - Delphi mailing list<BR>Post: delphi@delphi.org.nz<BR>Admin:
http://delphi.org.nz/mailman/listinfo/delphi<BR>Unsubscribe: send an email to
delphi-request@delphi.org.nz with Subject: unsubscribe
<P><FONT size=2 face=Arial>No virus found in this incoming message.<BR>Checked
by AVG - www.avg.com<BR>Version: 9.0.851 / Virus Database: 271.1.1/3061 -
Release Date: 08/16/10 18:35:00<BR></FONT></P>
<P><FONT size=2 face=Arial></FONT></P></BLOCKQUOTE></BODY></HTML>