StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><code>varchar(50)</code> and <code>varchar(128)</code> will behave pretty much identical from every point of view. The storage size is identical for values under 50 characters. They can be joined interchangeably (<code>varchar(50)</code> joined with <code>varchar(128)</code>) w/o type convertion issues (ie. an index on <code>varchar(50)</code> can seek a column <code>varchar(128)</code> in a join) and same applies to WHERE predicates. Prior to SQL Server 2012 ncreasing the size of a <code>varchar</code> column is a very fast metadata-only operation, after SQL Server 2012 this operation <em>may</em> be a slow size-of-data-update-each-record operation under certain conditions, similar to those descirbed in <a href="http://rusanu.com/2012/02/16/adding-a-nullable-column-can-update-the-entire-table/" rel="nofollow">Adding a nullable column can update the entire table</a>.</p> <p>Some issues can arrise from any column length change:</p> <ul> <li>application issues from handling unexpected size values. Native ones <em>may</em> run into buffer size issues if improperly codded (ie. larger size can cause buffer overflow). Managed apps are unlikely to have serious issues, but minor issues like values not fitting on column widths on screen or on reports may occur.</li> <li>T-SQL errors from truncating values on insert or update</li> <li>T-SQL silent truncation occuring and resulting in incorrect values (Eg. @variables declared as <code>varchar(50)</code> in stored proc)</li> <li>Limits like max row size or max index size may be reached. Eg. you have today a composite index on 8 columns of type <code>varchar(50)</code>, extending to <code>varchar(128)</code> will exceed the max index size of 900 and trigger warnings.</li> </ul> <p>Martin's warning about memory grants incresing is a very valid concern. I would just buy more RAM if that would indeed turn out to be an issue.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload