CODEWITHSUNDEEP
sqlsql-server
Vicky Thakor
Vicky Thakor

Reputation: 3916

Impact of altering table column size/length in SQL Server

Consider I've column VARCHAR(MAX). What if I change it to VARCHAR(500) will Microsoft SQL Server will decrease the size claimed by table?

If you got any link just comment it. I'll check it out.

Update:

I've tested following two case with table.

Initial Table size

enter image description here

ALTER TABLE table_transaction ALTER COLUMN column_name VARCHAR(500)
After ALTER column, table size is incresed enter image description here

Create new table with new column size and import data from old table
I've taken care of Index in new table.

enter image description here

Why table size is increased in case of ALTER COLUMN. Ideally table size should decrease.

After performing de-fragmentation on PK in original table few MB decreased. However its not promising like creating new table.

Upvotes: 1

Views: 7467

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46203

When you change varchar(n) column to varchar(MAX) or visa-versa, SQL Server will update every row in the table. This will temporarily increase table size until you rebuild the clustered index or execute DBCC CLEANTABLE.

For ongoing space requirements of a varchar(MAX) column, the space will be the same as varchar(n) as long as the value remains in-row. However, if the value exceeds 8000 bytes, it will be stored on separate LOB page(s) dedicated to the value. This will increase space requirements and require extra I/O when a query needs the value.

I good rule of thumb is to use MAX types only if the value may exceed 8000 bytes, and specify a proper max length for the domain of data stored for data 8000 bytes or less.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269773

According to the documentation, there is no difference in the storage of strings:

varchar [ ( n | max ) ]

Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB). The storage size is the actual length of the data entered + 2 bytes.

As I read this, the storage size is the actual length plus two bytes regardless of whether you use n or max.

I am suspicious about this. I would expect the length of varchar(max) to occupy four bytes. And there might be additional overhead for storing off-page references (if they exist). However, the documentation is pretty clear on this point.

Whether changing the data type changes the size of the field depends on the data already stored. You can have several situations.

  • If all the values are NULL, then there will be no changes at all. The values are not being stored.
  • If all the values are less than 20 bytes, then -- according to the documentation -- there would be no change. I have a nagging suspicion that you might save 2 bytes per value, but I can't find a reference to it and don't have SQL Server on hand today to check.
  • If values exceed 20 bytes but remain on the page, then you will save space because the values will change.
  • If the values go off-page, then you will save the header information as well as truncating the data (thank you Dan for pointing this out).

Upvotes: 1

Related Questions