>The only real downside to varchar is if you initially store a value in a varchar column with say, 10 characters and later change this value to say, 20 characters, the value gets fragmented into two separate physical areas on disk. Furthermore, if you change this same value to 30 characters you now have three non-contiguous areas that contain the value for the column. The database needs to do more work to construct this value, thereby causing a performance hit.
>
>So, with this said, if you know a column will rarely, if ever, change, then varchar is a good choice.
>
>HTH

Bob,

Increasing a varchar column's length will not cause any table fragmentation. The only way that table data gets fragmented is from a Clustered Key INSERT or an UPDATE that is performed on a row that will no longer fit on the page. This can happen when updating any varchar column, whether the max size has recently been increased or whether it has never changed.

For instance, consider a varchar(250) column that is part of a row that happens to reside in a nearly full page. The column's original value is 'Hello There'. SQL Server does not allocate more space than is required to store the data, regardless of the max size of the column. In the page, that column only uses 11 bytes and the next column's data begins at byte 12.

If the column gets updated with 'Four score and seven years ago...", one of two things will happen. If the row will still fit on the page, then the data in the row is shifted to make room. If the row will no longer fit in the page, SQL Server allocates a new page and moves the entire row to the new page. Keep in mind that this data shifting does not happen on disk. SQL Server performs disk IO in 8K pages, never on rows or columns. And this data shifting will happen whether the column is defined as varchar(33) or varchar(250).

If the column was originally varchar(11), then was changed to varchar(200), and the same UPDATE took place, the result would be exactly the same - the row is shifted to make room, or the row is copied to a new page.

All that being said, there can be performance issues with using varchar instead of char (with ANSI_PADDING ON) for a column that is part of clustered index in a high-volume OLTP system (1,000's of updates per second). Frequent updates of varchar columns that are part of a clustered index where the column data increases in physical size can cause table fragmentation when the FILLFACTOR is not set optimally.