none
Should I use varchar(<n>) or varchar(MAX) ?

    Question

  • Is there anything wrong with using varchar(MAX) for almost all varchar fields in all my tables?

    TIA,

    Barkingdog

    P.S No, I wouldn't use varchar(MAX) for ZIPCode or State (char(2)) data but I could use it for an Address fields, street names, or any other variable-length character data.   I don't think varchar(100) is any better or worse than varchar(MAX) from an overhead perspective.

     

    Wednesday, January 26, 2011 10:49 PM

Answers

  • From the storage prospective there is no difference (assuming, of course, you don't exceed 8000 bytes). Although there is the issue with the memory grants. SQL Server assumes that variable width columns are 50% full when estimates the size of the memory grant (4000 bytes for (max)). So if your variable width columns are populated for more than 50%, it increases the chance that query does not have enough memory especially for sort/hash operations that involves tempdb (performance hit). You can monitor it with sort/hash warnings in profiler.

    On other hand, you don't want to request memory the query does not need. First, it's waste of RAM. Second, if request fits in the different bucket, it could take longer to get the grant. So technically, ideally you want to use (N) which is 2 times bigger than avg size of the data.

    Check http://aboutsqlserver.com/2010/08/18/what-is-the-optimal-size-for-variable-width-columns/ for more details.

     


    Thank you!

    My blog: http://aboutsqlserver.com

    • Marked as answer by edm2 Thursday, February 03, 2011 12:53 AM
    Wednesday, February 02, 2011 4:55 PM

All replies

  • Hi,

    Incase of varchar(n)it accepts from 1 through 8,000. Incase of varchar(max) it accepts 2,147,483,647 characters.

    When the table contains the varchar(max) datatype their respective indexes can't be rebuilt online. So just have point in it while setting the data type as varchar(max).


     


    Thanks & Regards, Pramilarani.R
    Thursday, January 27, 2011 7:41 AM
  • I prefer using 'closest' datatype for the data. For example if you know that your id column will be less than 32000 why to declare it as INT? Or if it less than 255 then declare it as TINYINT...

    It is performance benefit in terms if you want to create index on such columns....I think VARCHAR(MAX) is not ideal candidate


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, January 27, 2011 7:51 AM
  • Uri,

     

    I agree totally with your perspective.  I just don't know if, while the idea appeals to me, if it based on fact or simply pleasing fantasy. (It's hard to find "hard" verifiable answers in Sql land).

     

    Barkingdog

    Thursday, January 27, 2011 3:18 PM
  • Then go just for the 'closest' datatype:-)


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Thursday, January 27, 2011 3:28 PM
  • From the storage prospective there is no difference (assuming, of course, you don't exceed 8000 bytes). Although there is the issue with the memory grants. SQL Server assumes that variable width columns are 50% full when estimates the size of the memory grant (4000 bytes for (max)). So if your variable width columns are populated for more than 50%, it increases the chance that query does not have enough memory especially for sort/hash operations that involves tempdb (performance hit). You can monitor it with sort/hash warnings in profiler.

    On other hand, you don't want to request memory the query does not need. First, it's waste of RAM. Second, if request fits in the different bucket, it could take longer to get the grant. So technically, ideally you want to use (N) which is 2 times bigger than avg size of the data.

    Check http://aboutsqlserver.com/2010/08/18/what-is-the-optimal-size-for-variable-width-columns/ for more details.

     


    Thank you!

    My blog: http://aboutsqlserver.com

    • Marked as answer by edm2 Thursday, February 03, 2011 12:53 AM
    Wednesday, February 02, 2011 4:55 PM
  • Dmitri,

     

    That is a totally wonderful answer! I was unaware of the points your raised. Thank you.

     

    Barkingdog

    Thursday, February 03, 2011 12:54 AM