title: Difference between varchar(5) and varchar(5000)?
body: <blockquote>
    <p><strong>Possible Duplicate:</strong><br>
    <a href="https://stackoverflow.com/questions/2009694/is-there-an-advantage-to-varchar500-over-varchar8000">is there an advantage to varchar(500) over varchar(8000)?</a>  </p>
  </blockquote>
  
  
  
  <p>In SQL Server we can specify the field type <code>varchar(n)</code>:</p>
  
  <blockquote>
    <p>Storage size is the actual length in bytes of the data entered, not n
    bytes.</p>
  </blockquote>
  
  <p>Often when planning a database structure I try to imagine the maximum possible length of strings which are to be stored in these <code>varchar</code> fields and then set that imagined size + some backup for the <code>varchar</code> field. Sometimes it may occur that the initially provided room is not enough for the field and then I need to increase the length.</p>
  
  <p>But actually, is it worth limiting the <code>varchar</code> field to some value and not just set something like <code>varchar(5000)</code> or even <code>varchar(max)</code> which will 100% suit the case? Is there any advantage maybe in page organization in SQL Server or index organization when I limit that <code>n</code> length?</p>
  
  <p><strong>Update</strong></p>
  
  <p>Here is what I mean (the advantage): <a href="https://stackoverflow.com/a/5353196/355264">https://stackoverflow.com/a/5353196/355264</a></p>
  
  <blockquote>
    <p>Yes, the query optimizer can guess how many rows fit in a page, if you
    have a lot of varchar fields that are larger than necessary, SQL
    Server can internally guess the wrong number of rows.</p>
  </blockquote>
  
  <p>Is the above true? And are there any other advantages?</p>