locked
Datatype Question Varchar(max), varchar(250), or char(250) RRS feed

  • Question

  •  

    I have a table that contains a lot of demographic information. The data is usually small (<20 chars) but ocassionally needs to handle large values (250 chars).  Right now its set up for varchar(max) and I don't think I want to do this. 

     

    How does varchar(max) store info differently from varchar(250)?  Either way doesn't it have to hold the container information?  So the word "Crackers" have 8 characters to it and information sayings its 8 characters long in both cases. This meaning its taking up same amount of space?

     

    Also my concern will be running queries off of it, does a varchar(max) choke up queries because the fields cannot be properly analyzed? Is varchar(250) any better?

     

    Should I just go with char(250) and watch my db size explode?

     

    Usually the data that is 250 characters contain a lot of blank space that is removed using a SPROC so its not usually 250 characters for long.

     

    Any insight to this would be appreciated.

    Thursday, October 18, 2007 2:16 PM

Answers

  • What I understand from your original post is that the data "is usually small (<20 chars) but ocassionally needs to handle large values (250 chars)."  Provided that there is no plan to get any larger data and these stated circumstances then I would suggest varchar(250).

     

    I also have to admit that leaving it alone still looks to me like a reasonable answer.  One of the things that I don't like doing is fixing things that aren't broken.  I guess I would like other opinions on this one.  Addional help please?

    Thursday, October 18, 2007 7:44 PM
  • If it were me starting from scratch, i'd go for varchar(250) for the following (simple) reasons.

     

    1) You data lengths are quite variable

    2) You know the maximum data length will be 250.

     

    However, I do agree with Kent that "if it ain't broke, don't fix it". As your strings are under 8000 characters the data will be stored just as it is for a regular varchar field so I don't think you're going to grab much/any extra performance with the change. Varchar(max) only behaves different for data over the 8000 limit.

     

    You could argue that changing it to varchar(250) would give better "meaning" to you data but as you know your application best, its really up to you decide whether this is important.

     

    HTH!
    Friday, October 19, 2007 9:05 AM

All replies

  • Base on what you are saying, I would definitely not change this to a CHAR(250) datatype.  The next question is what are the top 20 lengths of all strings in your table? Are any of them over 250 chars in length?  If the answer is that NONE of them are over 250 chars in length then you might want go with varchar(250).

     

     

     

     

    Thursday, October 18, 2007 7:00 PM
  • 250 is the longest.  Most of them will be 250 then there are some that are in the 20-30 range.  A few are decimal(18,2) and int.

     

    Thursday, October 18, 2007 7:08 PM
  • What I understand from your original post is that the data "is usually small (<20 chars) but ocassionally needs to handle large values (250 chars)."  Provided that there is no plan to get any larger data and these stated circumstances then I would suggest varchar(250).

     

    I also have to admit that leaving it alone still looks to me like a reasonable answer.  One of the things that I don't like doing is fixing things that aren't broken.  I guess I would like other opinions on this one.  Addional help please?

    Thursday, October 18, 2007 7:44 PM
  •  

    I am just looking at ways to improve performance... although its moving great right now, we will be utilizing the hardware for other items and I'm worried any design flaws will start to drag performance down.

     

    I guess the real question is, will changing it to varchar(250) improve performance?

    Thursday, October 18, 2007 8:33 PM
  • varchar(250) would help from an efficient space management point of view.

    Friday, October 19, 2007 8:19 AM
  • If it were me starting from scratch, i'd go for varchar(250) for the following (simple) reasons.

     

    1) You data lengths are quite variable

    2) You know the maximum data length will be 250.

     

    However, I do agree with Kent that "if it ain't broke, don't fix it". As your strings are under 8000 characters the data will be stored just as it is for a regular varchar field so I don't think you're going to grab much/any extra performance with the change. Varchar(max) only behaves different for data over the 8000 limit.

     

    You could argue that changing it to varchar(250) would give better "meaning" to you data but as you know your application best, its really up to you decide whether this is important.

     

    HTH!
    Friday, October 19, 2007 9:05 AM
  • Bear in mind that a VARCHAR field only allocates space on the server for the number of characters in the entry so if you entered 'Microsoft' then it would allocate space to 9 characters whereas a CHAR field will allocate the space regardless of whether it is filled or not.

     

    Space wise VARCHAR is always better than CHAR. CHAR is best used for small entries like a check digit code or something.

     

    Friday, October 19, 2007 1:23 PM