You guys are talking about the SQL data type uniqueidentifier, when you refer to UUID's, right? Or have I just missed the plot?
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/
