Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In our current project it just happens too often, that we need to extend columns by a couple of characters. From varchar(20) to varchar(30) and so on.

In reality, how much does it really matter? How good is this optimized? What is the impact of just allowing 100 or 200 or even 500 chars for normal "input" fields? An email can only have 320 chars, so ok - there is a good limit there. But what do I gain if I set it to 200, because I do not expect longer e-mail addresses than that.

Usually our tables will not have more than 100.000 rows, and up to 20 or 30 such columns.

We use SQL Server 2008 now, but it would be interesting to know how different DBs handle this issues.

In case the impact is very low - as I would expect, it would help to get some good arguments (backed up with links?) to convince my DBA, that this long-field-paranoia isn't really necessary.

In case it is, I'm here to learn :-)

share|improve this question
    
Thanks for asking this question! –  Jeff Mar 18 '11 at 15:56
add comment

7 Answers 7

up vote 8 down vote accepted

The specific answer to your question (at least for Oracle and probably other databases) is that the length of the field doesn’t matter, only the length of the data. However, this shouldn’t be used as a determining factor concerning whether to set the field to its maximum allowable length or not. Here are some other issues you should consider before maxing out field sizes.

Formatting Any client tool that formats the data based on the size of the fields will require special formatting considerations. Oracle’s SQL*Plus for example by default displays the maximum size of Varchar2 columns even if the data is only one character long. Compare…

create table f1 (a varchar2(4000), b varchar2(4000));
create table f2 (a varchar2(5), b varchar2(5));
insert into f1 values ('a','b');
insert into f2 values ('a','b');
select * from f1;
select * from f2;

Bad Data Field length provides an additional mechanism to catch/prevent bad data. An interface shouldn’t attempt to insert 3000 characters into a 100 character field, but if that field is defined to be 4000 characters, it just might. The error woudn’t be caught at the data entry stage, but the system may have trouble further down when another application tries to process the data and chokes. As an example, if you later decide to index the field in Oracle you would exceed the maximum key length (depending on block size and concatenation). See…

create index i1 on f1(a);

Memory If the client application allocates memory using the maximum size, the application would allocate significantly more memory than is necessary. Special considerations would have to be done to avoid this.

Documentation The size of the field provides another data point of documentation about the data. We could call all tables t1, t2, t3, etc. and all fields f1, f2, f3, etc., but by specifying meaningful names we better understand the data. For example, if an address table for a company with customers in the U.S. has a field called State that is two characters we expect the two character state abbreviation to go in it. On the other hand if the field is one hundred characters we might expect the full state name to go in the field.


All that being said, it does seem prudent to be prepared for change. Just because all your product names today fit in 20 characters doesn’t mean they always will. Don’t go overboard and make it 1000, but do leave room for plausible expansion.

share|improve this answer
    
See also stackoverflow.com/questions/1882073/…. –  Leigh Riffel Mar 17 '11 at 12:56
add comment

Here is a good starting point for you.

http://www.sqlskills.com/BLOGS/KIMBERLY/post/Disk-space-is-cheap.aspx

I may have misunderstood your original question. Let me see if I can find you a few other links for reference.

Here is good reference on data type selections: http://sqlfool.com/2009/05/performance-considerations-of-data-types/

Changing from varchar(20) to varchar(30) may seem like something small, but you need to understand more about how database structures work in order to be aware of the potential issues. For example, going to varchar(30) could push you past the tipping point of your columns (should all 30 bytes get used) being able to be stored on one page (less than 8060 bytes). This will lead to an increase in disk space used, a decrease in performance, and even some additional overhead with your transaction logs.

Here is a link for database structures: http://technet.microsoft.com/en-us/sqlserver/gg313756.aspx

Here is one for page splits and trx logging: http://sqlskills.com/BLOGS/PAUL/post/How-expensive-are-page-splits-in-terms-of-transaction-log.aspx

HTH

share|improve this answer
    
Giving just a link without summary isn't the kind of answers we like here on dba. I do not find the word varchar in that post even once. How can it answer this question. –  bernd_k Mar 16 '11 at 14:02
1  
I didn't take his question to be limited to just varchar, he tagged it as db design, and I thought it was helpful to provide him a link to a reputable resource discussing design choices. –  SQLRockstar Mar 16 '11 at 14:08
1  
also...he asked for links...i didn't think it was bad to point him to Kimberly. Perhaps you could share a link for him as well in your answer, since he asked for one? –  SQLRockstar Mar 16 '11 at 14:31
1  
Your second link points to sqlfool.com/content/PerformanceConsiderationsOfDataTypes.pdf And there is clearly said: 'The varchar datatype, by contrast, consumes only the amount of actual space used plus 2 bytes for overhead' –  bernd_k Mar 16 '11 at 15:32
    
yes, correct. let me edit my answer (again). –  SQLRockstar Mar 16 '11 at 15:34
add comment

I thought I'd share another interesting point, which I found in the following SO Question:

http://stackoverflow.com/questions/148398/are-there-any-disadvantages-to-always-using-nvarcharmax

Original answer by: Nick Kavadias

A reason NOT to use max or text fields is that you cannot perform [online index rebuilds][1] i.e. REBUILD WITH ONLINE= ON even with SQL Server Enterprise Edition.

[1]: http://msdn.microsoft.com/en-us/library/ms188388%28SQL.90%29.aspx "online index rebuilds"

I would consider this to be a big disadvantage when adding n/varchar(max) columns arbitrarily, and according to the MS Site this restriction against doing online index rebuilds remains in SQL Server 2008, 2008 R2 and Denali; so it's not specific to SQL Server 2005.

Thanks, Jeff

share|improve this answer
add comment

In some cases, the amount of space you allocate for a varchar field will affect the amount of memory allocated for in-memory sorts.

I found the presentations at SQLWorkshops.com thought provoking, this presentation talks about a case where a sort for an order by is spilling over into tempdb because not enough memory is being allocated for char/varchar fields.

http://webcasts2.sqlworkshops.com/webcasts.asp

This webcast was also presented as an article at the following website:

http://www.mssqltips.com/tip.asp?tip=1955

Note in this presentation that the column being sorted on is not the char/varchar column, but the amount of space allocated for the varchar column in memory makes a difference in the query performance in some cases.

share|improve this answer
    
+1 for his explanations of this. it's mandatory to watch his stuff :) –  cairnz May 11 '12 at 13:12
add comment

SET ANSI_PADDING ON?

You end up with a lot of trailing whitespace...

share|improve this answer
add comment

It matters only related to disk space and character length. Of course search on char data types and indexes on these type of data will act slower than integer but this is another discussion.

Varchar data type is a "variable" data type so if you set up a limit of varchar (500) than this is the maximum character length for that field. Minimum length can be between 0 and 500. On the other hand the disk space claimed will be different for 10, 30 or 500 character fields.

I did sometimes a test for data type varchar (800) and for null values I had 17 bytes used, and for each character inserted it added one more byte. For example a 400 character string had 417 bytes used on the disk.

share|improve this answer
add comment

I don't think, that there is any difference between tables created with columns of varchar(20) or varchar((8000), as long the the actual max length is <= 20.

On the other side, in some cases giving the users the possibility to store longer strings might encourage them to do it.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.