17

Possible Duplicate:
is there an advantage to varchar(500) over varchar(8000)?

In SQL Server we can specify the field type varchar(n):

Storage size is the actual length in bytes of the data entered, not n bytes.

Often when planning a database structure I try to imagine the maximum possible length of strings which are to be stored in these varchar fields and then set that imagined size + some backup for the varchar field. Sometimes it may occur that the initially provided room is not enough for the field and then I need to increase the length.

But actually, is it worth limiting the varchar field to some value and not just set something like varchar(5000) or even varchar(max) which will 100% suit the case? Is there any advantage maybe in page organization in SQL Server or index organization when I limit that n length?

Update

Here is what I mean (the advantage): https://stackoverflow.com/a/5353196/355264

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.

Is the above true? And are there any other advantages?

Community
  • 1
  • 1
net_prog
  • 9,921
  • 16
  • 55
  • 70
  • 1
    possible duplicate of [is there an advantage to varchar(500) over varchar(8000)?](http://stackoverflow.com/questions/2009694/is-there-an-advantage-to-varchar500-over-varchar8000) or of [varchar(max) everywhere?](http://stackoverflow.com/questions/2091284/varcharmax-everywhere) or of [SQL Server VARCHAR Column Width](http://dba.stackexchange.com/questions/11614/sql-server-varchar-column-width) – Martin Smith Jan 27 '12 at 12:24
  • RE: Your edit. See [my answer here](http://stackoverflow.com/questions/2009694/is-there-an-advantage-to-varchar500-over-varchar8000/5654947#5654947) – Martin Smith Jan 27 '12 at 12:46

2 Answers2

20

For one thing - you CANNOT index any column that's over 900 bytes in size.

That alone (to me) is a killer criteria against using varchar(5000) (or varchar(max)) all the time, even if not needed....

Use the appropriate size.

Also: varchar adds at least 2 bytes of overhead, so for strings of 10 characters or less, I would always recommend using char(n) instead.

char(5) will use 5 bytes of storage - always. varchar(5) uses between 2 and 7 bytes of storage.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 3
    So actually `varchar(15)` will be the same as `varchar(500)` in the terms of efficiency and speed, right? I.e. if I think the data will be around 15-20 bytes (maybe less or longer a bit) I can just specify `varchar(150)`? This is actually my main question. – net_prog Jan 27 '12 at 12:24
  • I don't understand that "always"... the question being between using varchar(500) or varchar(max) rules out any suggestion that the strings are usually going to be 10 characters or less. – Isaac Llopis Mar 18 '14 at 09:51
  • 1
    A column of type `char(100)` will **always** be exactly 100 characters long - even if you store only `Isaac` in it, it will be padded to the defined length (of 100 characters) with spaces - so really you're storing `Isaac....................` - which is highly inefficient – marc_s Mar 18 '14 at 09:52
  • 1
    The padding for a column depends on if you have set ANSI_PADDING ON/OFF. – Frank Mar 30 '16 at 14:36
15

MS SQL Server stores varchar(8000) and shorter differently from varchar(MAX).

The size at which MAX types are moved out of a table's data pages and into its own data pages is now fixed at 8,000 bytes if [large value types out of row] is set to OFF.

There are several reasons why you would use use varchar(n) rather than varchar(MAX)

Performance

varchar(n) is faster http://rusanu.com/2010/03/22/performance-comparison-of-varcharmax-vs-varcharn/

Indexing

varchar(900) and smaller can be indexed, but not varchar(MAX) or varchar(901) or larger

Compatibility

MS SQL Server Compact Edition does not support varchar(MAX)

Jeow Li Huan
  • 3,758
  • 1
  • 34
  • 52