none
varchar(max) vs varchar(255)

    Question

  •  

    Hi guys,

     

    I'm new to MS-SQL 2005, I will ask you a bunch of questions and I hope I will not be too boring hehe !

     

    I learned that in MS-SQL 2005, you can now use varchar(max), my question is why not always use varchar(max) since it will automatically use only the space needed ? Is there an advantage in specifying a size other than max when you use a varchar for an entry that can vary in length alot ?

     

    Thank you very much,

     

    Konnan

    Saturday, January 12, 2008 11:10 AM

Answers

  • There are a few diferences between VARCHAR(1-8000) and VARCHAR(MAX).

     

    when you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value. (only if the size exceeds 8000)

     

    VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of  row'. It means that the data row will have a pointer to another location where the 'large value' is stored. By default sql server will try to accomodate the value 'in row' but if it could not, it will store the large values 'out of row'. When values are stored 'out of row' there will be slight processing overhead in reading the information. Here is a good reference: http://msdn2.microsoft.com/en-us/library/ms189087.aspx

     

    I guess you cannot index a VARCHAR(MAX)/NVARCHAR(MAX) column.

     

    coming back to your question:

    I dont think it is bad to use VARCHAR(MAX) is bad. If you are storing smaller piecs of data in a VARCHAR(MAX) column, it will be treated as normal. If you dont want to index the column, then you can definitely go with VARCHAR(MAX) option.

     

    But most people do not advise that. First of all, by having a VARCHAR(MAX) will confuse some one who looks at the data later on. For example, if you want to store a comment of 100 characters or address of 80 characters, why should you go for VARCHAR(MAX)? If you use Address VARCHAR(MAX), comments VARCHAR(MAX), Name VARCHAR(MAX), some one trying to read or write data on a later date will be confused. They will not know what is the expected size of the data and they will be compelled to use LARGE VALUE types always.

     

    Again, these are not rules but conventions.

    Saturday, January 12, 2008 12:07 PM
    Moderator