none
varchar(255) or varchar(256) ?

    Question

  • We have always used varchar(255)  (if needed) in our project buts one the developers has started using varchar(256) instead of varchar(255)

    Is there any reason to prefer 255 over 256 or vice versa? (Especially under the newer versions of sql server. May be a throwback to a few years ago when RAM\Hardddisk  was not cheap).

    )

     

     

    TIA,

    Barkingdog

     


    • Edited by edm2 Wednesday, August 10, 2011 8:58 PM edit
    Wednesday, August 10, 2011 8:51 PM

Answers

  • I think one of the reason varchar(255) was widely used because in good old days certain file based programs like DBASE etc. can only hold upto 255 characters in a text field else you have to opt for a blob.

    and if you are curious what's difference between varchar(255) and varchar(256) then varchar(256) can hold upto 256 ascii characters instead of 255.

    Don't use varchar(255) as is, try to come up reasonable length that is suitable for the column e.g. firstname can be upto 30 characeters so use varchar(30) instead of VARCHAR(256) or varchar(255).


    >>Especially under the newer versions of sql server. May be a throwback to a few years ago when RAM\Hardddisk  was not cheap).

    I started using SQL Server since version 7 but many moons ago SQL Server varchar data type had a limitation of 255 characters.
    • Proposed as answer by Surendra Nath GM Wednesday, August 10, 2011 9:41 PM
    • Marked as answer by edm2 Saturday, August 13, 2011 4:50 AM
    Wednesday, August 10, 2011 9:28 PM

All replies

  • I think one of the reason varchar(255) was widely used because in good old days certain file based programs like DBASE etc. can only hold upto 255 characters in a text field else you have to opt for a blob.

    and if you are curious what's difference between varchar(255) and varchar(256) then varchar(256) can hold upto 256 ascii characters instead of 255.

    Don't use varchar(255) as is, try to come up reasonable length that is suitable for the column e.g. firstname can be upto 30 characeters so use varchar(30) instead of VARCHAR(256) or varchar(255).


    >>Especially under the newer versions of sql server. May be a throwback to a few years ago when RAM\Hardddisk  was not cheap).

    I started using SQL Server since version 7 but many moons ago SQL Server varchar data type had a limitation of 255 characters.
    • Proposed as answer by Surendra Nath GM Wednesday, August 10, 2011 9:41 PM
    • Marked as answer by edm2 Saturday, August 13, 2011 4:50 AM
    Wednesday, August 10, 2011 9:28 PM
  • There is no reason to prefer one over the other. A 50 character value will take up the same amount of space in the database with either definition. I don't spend to much time to identify the best length for a column and then provide an unecessary constraint on users of the database. Note that SQL server always stores the length of the data in two bytes. Some other database systems will store it in one byte if the maximumn length is less than 256.
    Tom G.
    Wednesday, August 10, 2011 9:51 PM
  • Note that SQL server always stores the length of the data in two bytes. Some other database systems will store it in one byte if the maximumn length is less than 256.
    Tom G.
    Hi,

    for varchar data will be stored one byte/character in SQL Server unless you use unicode NVARCHAR data type in which each character will take two bytes.

    Wednesday, August 10, 2011 11:09 PM
  • The length of the data stored is always a 2 byte integer regardless of NVARCHAR OR VARCHAR.
    Tom G.
    Wednesday, August 10, 2011 11:43 PM
  • The length of the data stored is always a 2 byte integer regardless of NVARCHAR OR VARCHAR.
    Tom G.

    I think you are referring to two different things here. Tome is referring to the overhead to keep track of how long the varchar value is. And that is two bytes regardless of varchar or nvarchar. And I believe that Chirag is referring to the actual data, where of course for varchar each letter uses one byte and for nvarchar each letter uses two bytes.
    Tibor Karaszi, SQL Server MVP | web | blog
    Thursday, August 11, 2011 6:57 AM
  • Tibor,

     

    You are correct about that.

    Thanks to the other writers too.

     

    Barkingdog

     

     

    Thursday, August 11, 2011 5:01 PM
  • As some others have implied, neither is correct. The length specified is the MAX length of the column--not the amount of space reserved. It's (somewhat) important to set this length if you want to limit the length of an acceptable data value being applied to the column. This means if a value is supplied that exceeds this length the INSERT will be rejected. If you lower it, I expect some applications will begin to fail where they had not before. Application front-ends should be written to limit user input to just this length and ask for a correction or simply truncate to this length. So 255, 256 or 2000 makes no difference in how much data is reserved--it does not work like that...

    hth


    __________________________________________________________________
    William Vaughn
    Author, Mentor, Trainer, MVP
    Beta V Corporation
    William Vaughn's blog

    “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”

    “The Owl Wrangler” a fantasy fiction novel

    Please click the Mark as Answer button if a post solves your problem!

    Thursday, August 11, 2011 5:23 PM
  • Varchar(255) is an old fossil from the past where many languages had a limitation of 255 characters for any string.  Most modern languages and databases won't care because their limits is usually much higher than that. However, as others have notified, it's best to use a limit that has some relevance to the possible maximum length of the underlying field - like varchar(30) for a firstname - because this help in documenting your stuff and the SQL-Server optimizer can do a better job if it can have a good knowledge of the maximum length for any record.

    Saturday, August 13, 2011 11:14 AM