UUID's are a cf function, CreateUUID() Creates a Universally Unique Identifier Each UUID returned by the CreateUUID function is a 35-character-string representation of a unique 128-bit integer
trent ---------- Original Message ---------------------------------- From: [EMAIL PROTECTED] Reply-To: "CFAussie Mailing List" <[EMAIL PROTECTED]> Date: Wed, 22 Jan 2003 15:37:48 +1100 >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/ > --- 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/
