General information
Forum:
Microsoft SQL Server
Keith,
Was this behaviour of re-writing pages or shifting data implemented in recent versions of SQL Server? I remember the fragmentation issue from classes I took many years ago.
I would guess that this shifting still has its performance downside, albeit only at the time of writing not at every time the data is read.
>
>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.
Previous
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only