Jim Lawrence
accessd at shaw.ca
Mon Aug 29 14:24:05 CDT 2005
My Thought on the whole thing is that if MS MDB has variable length fields it doesn't matter what size is requested and set, the data storage shrinks the minimum anyway. Now if we are referring to MS SQL server DB then fixed-fields sizes are set. Historically, all database programs that use variable length data storage were slower and more prone to corruption but if space is at a premium then that was required. But with sufficient space fixed size fields were faster and unless there was a hard drive failure always recoverable. My two cent worth. Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Bartow Sent: Monday, August 29, 2005 10:59 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Field Sizes Oh, OK, I'm a bit weary this morning, have a blistering headache and even after 4 advil and a pot of coffee didn't catch the kidding part, sorry, now that I look back it was obvious!. Good point about the padding with spaces though, so I guess it was worth it ;o) I originally wanted to discuss if upsizing from an access db to a SQL Server db was any more of a hassle based on needed field lengths or standard 255 field lengths. We know Access doesn't use fixed length storage for strings and you don't save any space by defining text fields as less than the maximum allowable. But IIRC SQL Server does so when upsizing you now have all 255 field lengths (which isn't terrible but not optimal) or do you go in and resize all the fields lengths? NOTE: I'm not taking sides here. I generally size fields similar to what Jim mentioned but I also have taken the easier route and done as Stuart mentioned - resetting the default field length size and (knowing how Access deals with text fields) not really caring if it was set smaller. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of dmcafee at pacbell.net Sent: Monday, August 29, 2005 12:39 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Field Sizes I "was" kidding when I said to make them all varcahr(8000). I tend to use 50. I prefer Varchars to chars, unless when dealing with a specific charcter length datatypes. Char's tend to pad the vales with spaces. So if you have a field titled fname, a varchar(50) would store the name Joe as "Joe" a char(50) would store the name Joe as "Joe " I tend to sometimes forget to RTRIM() my chars when selecting :( D -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Bartow Sent: Monday, August 29, 2005 10:01 AM To: 'Access Developers discussion and problem solving' Subject: RE: [AccessD] Field Sizes On that note, is it good advice to make all text fields VarCHar in SQL Server? -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of dmcafee at pacbell.net In SQL you can make everything a VARCHAR(8000) -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John Bartow Q: Does it matter when upsizing to SQL Server? -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan 255 characters. Access doesn't use fixed length storage for strings and you don't save any space by defining text fields as less than the maximum allowable. I change the default field size for text strings to 255 in Options and never change it for individual filed definitions. All that restricting field sizes does for you is increase the risk of truncating data or throwing up error messages at the user sometime in the future. -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com