15

We have a number of tables in our database that have VARCHAR(MAX) columns where a VARCHAR(500) (or something much smaller than max) will suffice. Naturally I want to clean these up, and get the sizes down to more reasonable levels. The 'how' to do this I understand: my question is what will altering these columns do to the pages and extants on disk? (There's lot of info out there about what happens when you grow a column, but having trouble finding info on what happens when you shrink one.)

Some of the tables have a very small row count, so I'm not worried about the cost of the change, but some are quite large, and I'm worried about them potentially being reorganized and causing a lot of blocking/downtime. In practical terms, I just want a way to estimate a maintenance window. In general, I'd like to understand better how the database engine behaves in this case.

Thanks in advance!

EDIT:

I have 20 tables I'm looking at, though only half of them have row counts greater than 1,000. The biggest has almost a million rows. The worst offender is a table with 350,000 rows and four VARCHAR(MAX) columns that can shrunk to the VARCHAR(500) level.

12

First things first: How much data is there in the table? Number of rows and size of the table?

Second: Can you back up and restore this table to a test server and run the alter statement to see the impact (assuming it is not unfeasible due to the table being too large to fit on a non-Production system)? I always find that testing in my environment is more accurate than advice from the interwebs since there are several factors that can influence the outcome that might not be provided in the question simply due to not knowing that those factors could affect the outcome.

Third: increasing the size of a variable-length field is (assuming you don't go over the 8060 byte limit) a simple meta-data operation since no actual data would be changing for such an operation. BUT, on the other hand, reducing the size of a variable-length field, even to something that will more than obviously work, is not a simple meta-data change because SQL Server doesn't know, prior to scanning all of the rows, that the newly requested size is valid.

Hence: Yes, this will lock the table for a period of time. How much time? Well, here is the test that I just did:

I had, from some other testing, a table with a single INT NOT NULL field and 1 million rows. I copied it to a new table for the purpose of doing this test via:

SELECT *, CONVERT(NVARCHAR(MAX), NEWID()) AS [StringField]
INTO dbo.ResizeTest
FROM dbo.ClusteredUnique;

This way I was starting with a similar scenario of having a MAX field (I just realized that you have VARCHAR and I am using NVARCHAR, but that shouldn't alter the behavior I am seeing) that I could then change to 500. And it has data in it that can easily fit within 500 characters. That took a few minutes.

I then ran:

ALTER TABLE dbo.ResizeTest ALTER COLUMN [StringField] NVARCHAR(500) NULL;

And that took just over 11 minutes.

I just re-ran the test again, this time dropping the [ResizeTest] table and changing both NVARCHARs to be just VARCHAR, just to be super-sure that I am comparing apples to something that at least looks like an apple ;-).

The initial table creation took 20 seconds while the ALTER TABLE took 2 minutes.

So, in terms of estimating downtime, that is really hard to do as it is based on disk I/O speeds, whether or not any auto-growth operations need to happen on the data file and/or the transaction log, etc. That is probably a large part of why my first test took 11 minutes to alter and the second, even with VARCHAR being half the size of the NVARCHAR data, took only 2 minutes (i.e. the files were pre-grown at that point). But still, you should keep in mind that my test is running on my laptop which is not the fastest disk, but it was also just 1 million rows of 2 small columns (22 or so bytes per row).

And since you asked what will it do to the data pages, here is your answer. I did an sp_spaceused after creating the table, after doing the ALTER COLUMN, and after doing ALTER TABLE dbo.ResizeTest REBUILD;. The results (the following numbers are based on the second test using VARCHAR, not the first test using NVARCHAR):

After initial table creation:        526,344 KB
After ALTER COLUMN VARCHAR(500):   1,031,688 KB  <--- !! Yikes!!
After ALTER REBUILD:                 526,472 KB

If you are concerned about needing to keep the operation to the shortest time possible, check out an article that I wrote about doing just that: Restructure 100 Million Row (or more) Tables in Seconds. SRSLY! (free registration required).

  • 2
    So I copied the worst table to my local instance (i.e., slower disk and 1/3 the cores). I ALTERed each column in succession - each action took less than a second. By the time they were done, the table had doubled in size, but once I did a REBUILD (which was also a sub-second operation), the table went back to its original size. – nateirvin Jan 30 '15 at 17:02
  • @nateirvin That is good to hear. You can probably speed up the ALTER TABLE operation by doing all fields in one shot, separating each column with a comma. If the transaction is too big then split the table into 2 ALTER statements of half of the columns each. And depending on how big the table gets, you can even doing a REBUILD between each of the two ALTER statements. Something to play with. Also, keep in mind that the operation will probably take a schema-lock for the duration which will block all access to the table. – Solomon Rutzky Jan 30 '15 at 17:06
  • 1
    I did each ALTER separately so I could track the size changes between each one, but definitely good to know. Thanks! – nateirvin Jan 30 '15 at 17:52
1

From what I've gathered running the alter statement shouldn't take very long as long at the table isn't locked by another process. According to gbn it's just a metadata change: https://stackoverflow.com/questions/7261909/is-it-bad-to-use-alter-table-to-resize-a-varchar-column-to-a-larger-size

Also, as for how it's stored, it seems that SQL Server stored the varchar data in an 8k page until it fills up an entire page, which at this point it replaces it with a pointer and stores it as a BLOB.

I'm assuming that when you change the length, you won't be truncating any records. If so, then at max the data that you're converting to varchar(500) should be, at most, 502 bytes long and shouldn't have a pointer.

So, long story short, not much should change as long as you're not truncating any data.

  • 5
    This is absolutely incorrect. I won't downvote because you actually tested it (which is more than some folks do, so thanks for doing that), but you need to test this at scale. The answer you linked to was about increasing the size, not decreasing. Those are two very different operations. – Solomon Rutzky Jan 28 '15 at 22:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.