Hi Scott, Came across this issue a couple of years back after I had just moved to MS SQL and discovered that there were Transact-SQL functions that did this which we implemented for a while.
However, in all recent work I explicitly set the id by using the MAX_ID custom tag inside a transaction - which gives the advantage of not being dependent on MSSQL. This tag comes from the Fusebox library and you put it inside a transaction with the insert statement and it gets the max id in a table and returns a number one greater than it, which you use to insert the values. I also have a modified version of this which picks a random ID and checks that it doesn't exist. This is only suitable for tables which do not have large amounts of data but have a need to stop people guessing ID's. I'll happily send you the tags, but the maxID one should be on the fusebox site. Regards, Mark > This is a multi-part message in MIME format. > > ------=_NextPart_000_0020_01C2C1F2.9B325A50 > Content-Type: text/plain; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > Sorry to ask probably a question thats had a debate before, but.. i will = > so shutup and read. :D > > Whats the negative sideeffects of using UUID instead of Integer = > (autonumber) for primary keys in a table. The reason i bring this up, as = > I always use a parent-child-sibling-infant type relationship amongst = > various tables, one example off the top of my head was a awhile back = > where i had 3 tables that basically provide: > > - Category (parent table) > - Category (parent table) > ---- File (child table) > ---- File (child table) > ------- file versions (sibling table) > ------- file versions (sibling table) > ------- file versions (sibling table) > ---- File (child table) > ---- File (child table) > ------- file versions (sibling table) > ------- file versions (sibling table) > ------- file versions (sibling table) > - Category (parent table) > ---- File (child table) > ---- File (child table) > ---- File (child table) > ---- File (child table) > - Category (parent table) > > This situation called for the ability to not only associate 3 different = > tables into a heircahy based dataset, but it also called for i think 3-4 = > other tables that provided other pieces of information per level, and in = > doing this i was forced to use a big meaty looking SQL SP, that made use = > of both UNIONs and Views, which worked great.=20 > > I did find that if i simply relied on the Autonumber system, the = > heirachy would be out of sync, as in the joining field for the child had = > the same value as the parent pk, but having said that, the child pks = > also had the same value and so a child brach would mistakenly associate = > its children under another child? > > ie: > parentID =3D 2 > > childID =3D 1 > childParent =3D 2 > > childID =3D 2 > childParent =3D 2 > > childID =3D 3 > childParent =3D 2 > > even though the childParent =3D 2, i did find that child referred to = > childID =3D 2 as its parent? > > I did manage to find away around this so i didn't have to basically = > redesign the entire db, but i'm about to embark on a bigger project = > (probably the biggest i have undertaken yet) and i so want this to be a = > finely tuned effecient running machine, and having remembering this = > scenario, i felt a bit nervous with simply utilising an "autonumbered" = > system or even a seperate table which has a running pool of numbers that = > you take from. > > Atleast with UUID i know that no matter what no other rowID through-out = > the entire database will have the same value? > > Your thoughts would be appreciated. > > Scott Barnes=20 > eCommerce > Tourism Queensland / Sunlover Holidays > [EMAIL PROTECTED]=20 > > www.queenslandtravel.com=20 > www.sunloverholidays.com=20 > > > > ------=_NextPart_000_0020_01C2C1F2.9B325A50 > Content-Type: text/html; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> > <HTML><HEAD> > <META http-equiv=3DContent-Type content=3D"text/html; = > charset=3Diso-8859-1"> > <META content=3D"MSHTML 6.00.2800.1106" name=3DGENERATOR> > <STYLE></STYLE> > </HEAD> > <BODY> > <DIV><FONT face=3DArial size=3D2>Sorry to ask probably a question thats = > had a debate=20 > before, but.. i will so shutup and read. :D<BR></FONT></DIV> > <DIV><FONT face=3DArial size=3D2>Whats the negative sideeffects of using = > UUID=20 > instead of Integer (autonumber) for primary keys in a table. The reason = > i bring=20 > this up, as I always use a parent-child-sibling-infant type relationship = > amongst=20 > various tables, one example off the top of my head was a awhile back = > where i had=20 > 3 tables that basically provide:</FONT></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV><FONT face=3DArial size=3D2>- Category (parent table)</FONT></DIV> > <DIV><FONT face=3DArial size=3D2>- Category (parent table)</FONT></DIV> > <DIV><FONT face=3DArial size=3D2>---- File (child = > table)</FONT></DIV> > <DIV> > <DIV><FONT face=3DArial size=3D2>---- File (child = > table)</FONT></DIV> > <DIV><FONT face=3DArial size=3D2>------- file versions (sibling = > table)</FONT></DIV> > <DIV><FONT face=3DArial size=3D2>------- file versions (sibling = > table)</FONT></DIV> > <DIV><FONT face=3DArial size=3D2>------- file versions (sibling=20 > table)</FONT></DIV></DIV> > <DIV> > <DIV><FONT face=3DArial size=3D2>---- File (child = > table)</FONT></DIV> > <DIV> > <DIV><FONT face=3DArial size=3D2>---- File (child = > table)</FONT></DIV> > <DIV><FONT face=3DArial size=3D2>------- file versions (sibling = > table)</FONT></DIV> > <DIV><FONT face=3DArial size=3D2>------- file versions (sibling = > table)</FONT></DIV> > <DIV><FONT face=3DArial size=3D2>------- file versions (sibling=20 > table)</FONT></DIV></DIV></DIV> > <DIV><FONT face=3DArial size=3D2>- Category (parent table)</FONT></DIV> > <DIV> > <DIV><FONT face=3DArial size=3D2>---- File (child = > table)</FONT></DIV> > <DIV> > <DIV><FONT face=3DArial size=3D2>---- File (child = > table)</FONT></DIV> > <DIV> > <DIV><FONT face=3DArial size=3D2>---- File (child = > table)</FONT></DIV> > <DIV> > <DIV><FONT face=3DArial size=3D2>---- File (child=20 > table)</FONT></DIV></DIV></DIV></DIV></DIV> > <DIV><FONT face=3DArial size=3D2>- Category (parent table)</FONT></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV><FONT face=3DArial size=3D2>This situation called for the ability = > to not only=20 > associate 3 different tables into a heircahy based dataset, but it also = > called=20 > for i think 3-4 other tables that provided other pieces of information = > per=20 > level, and in doing this i was forced to use a big meaty looking SQL SP, = > that=20 > made use of both UNIONs and Views, which worked great. </FONT></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV><FONT face=3DArial size=3D2>I did find that if i simply relied on = > the=20 > Autonumber system, the heirachy would be out of sync, as in the joining = > field=20 > for the child had the same value as the parent pk, but having said that, = > the=20 > child pks also had the same value and so a child brach would mistakenly=20 > associate its children under another child?</FONT></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV><FONT face=3DArial size=3D2>ie:</FONT></DIV> > <DIV><FONT face=3DArial size=3D2>parentID =3D 2</FONT></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV><FONT face=3DArial size=3D2>childID =3D 1</FONT></DIV> > <DIV><FONT face=3DArial size=3D2>childParent =3D 2</FONT></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV><FONT face=3DArial size=3D2>childID =3D 2</FONT></DIV> > <DIV><FONT face=3DArial size=3D2>childParent =3D 2</FONT></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV> > <DIV><FONT face=3DArial size=3D2>childID =3D 3</FONT></DIV> > <DIV><FONT face=3DArial size=3D2>childParent =3D 2</FONT></DIV></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV><FONT face=3DArial size=3D2>even though the childParent =3D 2, i = > did find that=20 > child referred to childID =3D 2 as its parent?</FONT></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV><FONT face=3DArial size=3D2>I did manage to find away around this = > so i didn't=20 > have to basically redesign the entire db, but i'm about to embark on a = > bigger=20 > project (probably the biggest i have undertaken yet) and i so want this = > to be a=20 > finely tuned effecient running machine, and having remembering this = > scenario, i=20 > felt a bit nervous with simply utilising an "autonumbered" system or = > even a=20 > seperate table which has a running pool of numbers that you take=20 > from.</FONT></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV><FONT face=3DArial size=3D2>Atleast with UUID i know that no matter = > what no=20 > other rowID through-out the entire database will have the same=20 > value?</FONT></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV><FONT face=3DArial size=3D2>Your thoughts would be = > appreciated.</FONT></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV> > <P><FONT face=3DArial size=3D2>Scott Barnes <BR>eCommerce<BR>Tourism = > Queensland /=20 > Sunlover Holidays<BR><A=20 > href=3D"mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED]</A> = > <BR></FONT><FONT=20 > face=3DArial size=3D2><BR><A=20 > href=3D"http://www.queenslandtravel.com";>www.queenslandtravel.com</A>=20 > <BR>www.sunloverholidays.com </FONT></P></DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> > <DIV><FONT face=3DArial size=3D2></FONT> </DIV> ---<BR> You are currently subscribed to cfaussie as: [email protected]<BR> To unsubscribe send a blank email to [EMAIL PROTECTED]<BR> <BR> MX Downunder AsiaPac DevCon - http://mxdu.com/ </BODY></HTML> > > ------=_NextPart_000_0020_01C2C1F2.9B325A50--
