It appears all my messages do not appear in the order I posted them, anyway, I'm sure you can figure out in which order they were posted...
> Grant, > > Correct me if I am wrong, but I believe the most common use for the UUID's is as you >specify for replication, however not being used as the primary key. > > To overcome the problems you mention with replication you should specify the [NOT >FOR REPLICATION] option on your primary key, which I (or should say most db devs) >suggest should always be an Integer or BIG integer (9,223,372,036,854,775,807) if you >expect more than 2,147,483,647 records in your table. > > Thats my 2 cents... > ;-) > Taco Fleur > > > > > We use UUID's for all our applications as the primary key. > > > > Our main driver behind this is syndication of data between separate > > DB's. I.e you have 2 separate databases (dev and production) and want to > > merge some data. If both databases have had data added independently of > > the other then it is possible the same integer ID will exists is both > > DB's making it very difficult for merge replication. > > > > This also means we also have database independence and do not have to > > rely on any DB vendors specific features. > > > > With regards to speed we have run some SQL trace tests and found that > > for tables with less than 10,000 records it's not an issue. It also > > helps If you index the uuid field. > > > > > > Hope this helps. > > > > > > > > Grant Straker > > Straker > > Next Generation Content Management > > www.straker.com.au > > > > > > > > > > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > > Sent: Wednesday, 22 January 2003 1:20 p.m. > > To: CFAussie Mailing List > > Subject: [cfaussie] Re: UUID vs Integer with Primary keys.. > > > > > > OK, I am posting the scripts for the SPs. > > > > You should only look at the process of the SPs, to really use them, you > > will have to create your own tables to work with. And modify the column > > and table names in the SPs... I was going to post the whole structure of > > the db as well, but it will take me to long to modify and adjust for > > posting. > > > > If anyone sees any faults in this script or knows a better way of doing > > it, I would be happy to hear about it. It took me a long time to get > > these SPs to create a fast and efficient hierachy.. > > > > While we are talking about SPs, anyone got any good solutions to client > > side cursors? i.e. working with a cursor in the db is fine and dandy, > > but as soon as you transfer the data to the client (web) it looses it's > > cursor features. I was working on a cursor that doesn't loose it's state > > and maintain the session with the client, but it ended up using to many > > temp tables... I hope I'm not off topic here, I know its a CF list, but > > after all I am a CF programmer ;-)) > > > > Stored Proc 1 > > --------------------------------------------- > > SET QUOTED_IDENTIFIER OFF > > GO > > SET ANSI_NULLS OFF > > GO > > > > /****** > > Object: Stored Procedure dbo.spTree Script Date: 22/01/2003 9:51:54 > > AM > > Author: Taco Fleur > > ******/ > > CREATE Procedure spTree > > ( > > @parentID INTEGER > > ) > > AS > > > > SET NOCOUNT ON > > > > DECLARE @table VARCHAR(64), @sql NVARCHAR(1024), @position INTEGER SET > > @position = 0 > > > > SET @table = '##spTree' + CONVERT( VARCHAR(4), @@SPID ) > > > > SET @sql = 'CREATE TABLE ' + @table > > +' (' > > +' position INTEGER, ' > > +' pkReplyID INTEGER, ' > > +' fkParentID INTEGER, ' > > +' fkThreadType TINYINT, ' > > +' replyBody VARCHAR(8000), ' > > +' approved BIT, ' > > +' dateStamp SMALLDATETIME ' > > +')' > > > > EXECUTE sp_executesql @sql > > > > -- Get all immediate replies to the thread > > -- threadType = 1 AND fkParentID = threadID > > EXECUTE spTreeBuild '1', @parentID, @table, @position > > > > -- The tree is now ready in the temp table. > > -- Construct SQL statement for selecting from table. > > SET @sql = 'SELECT * FROM ' + @table > > > > -- Execute SQL to select. > > EXECUTE sp_executesql @sql > > > > -- Construct SQL statement for dropping the temp table. > > SET @sql = 'DROP TABLE ' + @table > > > > -- Execute SQL to drop the table. > > EXECUTE sp_executesql @sql > > > > RETURN > > GO > > > > SET QUOTED_IDENTIFIER OFF > > GO > > SET ANSI_NULLS ON > > GO > > > > --------------------------------------------- > > Stored Proc 2 > > --------------------------------------------- > > /****** Object: Stored Procedure dbo.spTreeBuild Script Date: > > 22/01/2003 9:52:11 AM ******/ > > CREATE Procedure spTreeBuild > > ( > > @threadType TINYINT, > > @parentID INTEGER, > > @table VARCHAR(64), > > @position INTEGER > > ) > > AS > > > > SET NOCOUNT ON > > > > DECLARE @replyID INTEGER, @replyBody VARCHAR(8000), @approved BIT, > > @dateStamp SMALLDATETIME > > > > DECLARE @sql NVARCHAR(1024) > > > > DECLARE @children CURSOR > > > > SET @position = @position + 1 > > > > EXECUTE spTreeChild @parentID, @threadType, @children OUTPUT, @position > > > > -- Loop to parse the list of activities obtained in cursor. WHILE( 1 = 1 > > ) BEGIN > > -- Fetch the details for next activity. > > FETCH NEXT FROM @children > > INTO @position, @replyID, @parentID, @threadType, @replyBody, > > @approved, @dateStamp > > > > -- Check if fetch is valid, i.e., there are more records. > > IF @@FETCH_STATUS = -1 BREAK > > > > SET @sql = 'INSERT INTO ' + @table + ' VALUES(' > > + CAST(@position AS VARCHAR(10)) + ',' > > + CAST(@replyID AS VARCHAR(10)) + ',' > > + CAST(@parentID AS VARCHAR(10)) + ',' > > + CAST(@threadType AS VARCHAR(10)) + ',' > > + '''' + @replyBody + ''',' > > + CAST(@approved AS VARCHAR(10)) + ',' > > + '''' + CAST(@dateStamp AS VARCHAR(20)) + ''')' > > > > EXECUTE sp_executesql @sql > > > > EXECUTE spTreeBuild '2', @replyID, @table, @position > > END > > > > -- Close the cursor. > > CLOSE @children > > > > -- Deallocate the cursor. > > DEALLOCATE @children > > > > RETURN > > GO > > > > SET QUOTED_IDENTIFIER OFF > > GO > > SET ANSI_NULLS ON > > GO > > > > --------------------------------------------- > > Stored Proc 3 > > --------------------------------------------- > > /****** Object: Stored Procedure dbo.spTreeChild Script Date: > > 22/01/2003 9:52:24 AM ******/ > > CREATE Procedure spTreeChild > > ( > > @parentID INTEGER, > > @threadType TINYINT, > > @child CURSOR VARYING OUTPUT, > > @position INTEGER > > ) > > AS > > > > SET NOCOUNT ON > > > > SET @child = CURSOR > > FOR SELECT @position AS position, > > pkReplyID, > > fkParentID, > > fkThreadType, > > replyBody, > > approved, > > dateStamp > > FROM tblForumReply > > WHERE (fkParentID = @parentID) > > AND (fkThreadType = @threadType) > > ORDER BY datestamp > > > > OPEN @child > > > > RETURN > > GO > > > > SET QUOTED_IDENTIFIER OFF > > GO > > SET ANSI_NULLS ON > > GO > > > > --------------------------------------------- > > > > I would appreciate it if these stored procs won't go any further than > > this list, when time comes I'd like to publish it myself ;-)) > > > > Taco Fleur > > > > > > > > > > > > > > > This email is to be read subject to the disclaimer below. > > > > > > Taco, > > > > > > The one table approach you describe is something we use too, so those > > > stored procedures sound interesting to me. Could you please send me a > > > copy of them? > > > > > > Cheers > > > > > > Vik > > > --------------------- > > > Viktor Radnai > > > Web Developer, National E-Commerce, Ernst & Young > > > Direct: +61 2 9248 4361 > > > > > > > > > > > > > > > > > [EMAIL PROTECTED] > > > > > Sent by: To: > > "CFAussie Mailing List" <[EMAIL PROTECTED]> > > > [EMAIL PROTECTED] cc: > > > > > mon.com.au Subject: > > [cfaussie] Re: UUID vs Integer with Primary keys.. > > > > > > > > 22/01/2003 10:06 AM > > > > > Please respond to "CFAussie > > > > > Mailing List" > > > > > > > > > > > > > > > > > > > > > > > > Scott, > > > > > > I'm not sure why you would want to spread your data across 3 tables. > > > > > > I always use the following db structure for a Tree Hierachy. > > > > > > pkWhateverID [integer pk identity] / fkParentID [integer] / textColumn > > > > > [varchar(X)] > > > > > > I use 3 very small and simple stored procedures that create a temp > > > table and create the sorted output I need to display. I'd be happy to > > > send you a copy of these 3 stored procedures if it would be of any > > > help to you? > > > > > > The disadvantage I see is that with the structure you got now you can > > > not go down the Tree hierachy further than the sibling. With the above > > > > > structure you can go down the hierachy as far as you need to. > > > > > > I would say the negative effect of using UUID would be: > > > - It takes longer to create than an incrementing integer > > > - I believe there is still a 0.01 % change that you will run into a > > > duplicate > > > - It takes more space to store a UUID instead of an integer > > > > > > Hey, maybe I am way of here and don't exactly understand what needs to > > > > > be done... In anycase if I am on the right track let me know if I can > > > help.. > > > > > > Taco Fleur > > > > > > PS. sounds like you got the perfect job being able to work on thsoe > > > sites. > > > > > > > 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. > > > > > > > > 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 = 2 > > > > > > > > childID = 1 > > > > childParent = 2 > > > > > > > > childID = 2 > > > > childParent = 2 > > > > > > > > childID = 3 > > > > childParent = 2 > > > > > > > > even though the childParent = 2, i did find that child referred to > > > childID = 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 > > > > eCommerce > > > > Tourism Queensland / Sunlover Holidays [EMAIL PROTECTED] > > > > > > > > www.queenslandtravel.com > > > > www.sunloverholidays.com > > > > > > --- > > > You are currently subscribed to cfaussie as: > > > [EMAIL PROTECTED] To unsubscribe send a blank email to > > > [EMAIL PROTECTED] > > > > > > MX Downunder AsiaPac DevCon - http://mxdu.com/ > > > > > > > > > > > > > > > -------------------- > > > NOTICE - This communication contains information which is confidential > > > > > and the copyright of Ernst & Young or a third party. > > > > > > If you are not the intended recipient of this communication please > > > delete and destroy all copies and telephone Ernst & Young on 1800 655 > > > 717 immediately. If you are the intended recipient of this > > > communication you should not copy, disclose or distribute this > > > communication without the authority of Ernst & Young. > > > > > > Any views expressed in this Communication are those of the individual > > > sender, except where the sender specifically states them to be the > > > views of Ernst & Young. > > > > > > Except as required at law, Ernst & Young does not represent, warrant > > > and/or guarantee that the integrity of this communication has been > > > maintained nor that the communication is free of errors, virus, > > > interception or interference. > > > > > > Liability limited by the Accountants Scheme, approved under the > > > Professional Standards Act 1994 (NSW) > > > -------------------- --- You are currently subscribed to cfaussie as: [email protected] To unsubscribe send a blank email to [EMAIL PROTECTED] MX Downunder AsiaPac DevCon - http://mxdu.com/
