[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