Auto Incrementing Seed vs CF UUID
So, anyone have any thoughts on which are better... Using CF8 Ent, SQL 2k5 Ent and an enterprise application with millions of rows in the table? Thanks for all your feedback, Brian ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340158 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Auto Incrementing Seed vs CF UUID
Auto Incrementing Seed - bigint (max 9,223,372,036,854,775,807 rows) or int (max 2,147,483,647 rows) CF UUID (max functionally infinite rows) ...so choose the smallest one of those that is large enough to be future-proof for your table. NOTE: when I use UUID, I don't use CF UUID. Instead, I use SQL Server's newid() which *should* almost always be faster. On Mon, Dec 20, 2010 at 11:34 AM, Brian Polackoff bpolack...@gmx.comwrote: So, anyone have any thoughts on which are better... Using CF8 Ent, SQL 2k5 Ent and an enterprise application with millions of rows in the table? Thanks for all your feedback, Brian ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340160 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Auto Incrementing Seed vs CF UUID
If you are using it as an ID, I go with UUID. Makes it a little harder to put into the URL to look up things they shouldn't. On Mon, Dec 20, 2010 at 9:34 AM, Brian Polackoff bpolack...@gmx.com wrote: So, anyone have any thoughts on which are better... Using CF8 Ent, SQL 2k5 Ent and an enterprise application with millions of rows in the table? Thanks for all your feedback, Brian ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340161 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Auto Incrementing Seed vs CF UUID
Using UIDs makes it much easier to move data between servers or databases or even tables later, too, if you ever need to. For instance, moving records from production down to staging, when there's been testing going on in staging ... may have 100 records with IDs between 105 and 204 in each database, and all the related child records are keyed off of them etc ... makes it very easy to move everything around when using UIDs. In general, I just find the fact that the ID really is unique to *that* record very useful. From: Phillip Vector vec...@mostdeadlygame.com Sent: Monday, December 20, 2010 12:58 PM To: cf-talk cf-talk@houseoffusion.com Subject: Re: Auto Incrementing Seed vs CF UUID If you are using it as an ID, I go with UUID. Makes it a little harder to put into the URL to look up things they shouldn't. On Mon, Dec 20, 2010 at 9:34 AM, Brian Polackoff bpolack...@gmx.com wrote: So, anyone have any thoughts on which are better... Using CF8 Ent, SQL 2k5 Ent and an enterprise application with millions of rows in the table? Thanks for all your feedback, Brian ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340162 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Auto Incrementing Seed vs CF UUID
So, anyone have any thoughts on which are better... Using CF8 Ent, SQL 2k5 Ent and an enterprise application with millions of rows in the table? Do you plan to use these records anywhere other than this database server (replication, migration)? If so, use a UUID. But you might want to let the database generate the UUID for you. If none of these conditions apply, you're probably better off using an identity column with an integer data type. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340163 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Auto Incrementing Seed vs CF UUID
If you are using it as an ID, I go with UUID. Makes it a little harder to put into the URL to look up things they shouldn't. If your applications allow people to substitute values and see things they shouldn't see, you have bigger problems than integers vs UUIDs. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340165 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Auto Incrementing Seed vs CF UUID
Of course I check the values and such when I need to. But for something like a I forgot my password system, I'd rather give them a key of a UUID then an integer. On Mon, Dec 20, 2010 at 10:44 AM, Dave Watts dwa...@figleaf.com wrote: If you are using it as an ID, I go with UUID. Makes it a little harder to put into the URL to look up things they shouldn't. If your applications allow people to substitute values and see things they shouldn't see, you have bigger problems than integers vs UUIDs. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ http://training.figleaf.com/ Fig Leaf Software is a Veteran-Owned Small Business (VOSB) on GSA Schedule, and provides the highest caliber vendor-authorized instruction at our training centers, online, or onsite. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340170 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Auto Incrementing Seed vs CF UUID
Generally, unless you have a specific need to use UUIDs/GUIDs, such as in replication scenarios, integers are the better choice. Kimberly Tripp has a great presentation on this subject if you ever get a chance to take one of her classes. She spends an hour detailing all the ways the use of GUIDs negatively impact database performance. If using UUIDs, let SQL Server generate the values for you. SQL Server has a NEWSEQUENTIALID function that generates GUIDs in incrementing order, which can significantly reduce the fragmentation in the database. The use of this SQL Server 2005 feature can have a significant impact on the speed of large databases, although integers will always be faster. You could use both, having an integer as the main clustered PK, while maintaining a GUID as an alternate primary key, with a non-clustered index, in case you want to use that to look up a record. Kimberly Tripp is an authority on this subject and has lot of information on her Web site and in the PowerPoint slides from her classes. Her PowerPoint slides might not be on her public Web site. -Mike Chabot http://www.linkedin.com/in/chabot http://www.linkedin.com/in/chabot On Mon, Dec 20, 2010 at 12:34 PM, Brian Polackoff bpolack...@gmx.comwrote: So, anyone have any thoughts on which are better... Using CF8 Ent, SQL 2k5 Ent and an enterprise application with millions of rows in the table? Thanks for all your feedback, Brian ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340178 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
