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/ > > --- > 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/ > > --- > 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/ --- 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/
