0

I have a large table with a TEXT column. I ALTERed the column to VARCHAR(MAX) using the following statement:

ALTER TABLE MyTable
ALTER COLUMN Details VARCHAR(MAX)

I read that the difference between TEXT and the new equivalent VARCHAR(MAX) is that records with less than 8k chars are stored as plain text rather than BLOB internally.

Running the statement above took less than one second so I assume that no reorganizing was done. I wonder if there is any stored procedure or something like that to do so?

As less than 2 percent of the records in my table have more than 8k chars I wonder if I could get a performance boost when selecting the Details column if the SQL Server would change the way it stores the data?!

nvm-uli
  • 626
  • 1
  • 7
  • 14
  • 1
    No reorganizing is done with an alter like that. If you change the data type and it causes page splits, SQL Server won't clean it up for you unless you explicitly issue a reorganize command – dfundako May 10 '17 at 14:04
  • Thank you. Can't find any reorganize command apart from the one for indexes and statistics. Any hint? – nvm-uli May 10 '17 at 15:20
  • 1
    Either rebuild/reorganize the table if its a heap or rebuild/reorg the clustered index – dfundako May 10 '17 at 15:22
  • @dfundako Alter like this DO NOT cause any page splits, it does not even touch data pages; this operation is metadata only, nothing is changed in existing rows and you can easily check it using dbcc ind + dbcc page – sepupic May 10 '17 at 20:22
  • @dfundako >>>Either rebuild/reorganize the table if its a heap or rebuild/reorg the clustered index??? 1) How can you reorganize a heap? 2) Rebuilding clustered table containing LOB values that had text as a type that was altered to varchar(max) WILL NOT cause LOB values to move in row – sepupic May 10 '17 at 20:28

1 Answers1

1

The actual storage will not be change until you update the existing rows. I cannot find any proof link now but it works similar to how it was with text_in_row option. When you had a table with text column and decided to change the table option "text in row" (so the short strings could be stored in row) no change was made until you updated the existing rows

sepupic
  • 8,409
  • 1
  • 9
  • 20
  • So you would recommend something like this? `UPDATE MyTable SET Details = Details` Or is there some sort of `REORGANIZE` command such there is for indexes and statistics? – nvm-uli May 10 '17 at 14:44
  • 1
    Niether rebuild nor reorganize will constrain LOB values to move in row, the only thing that can do it is UPDATE. So if it's really matter to you, you'd perform this update, otherwise leave it as it is, the new rows will be fit in row if they can – sepupic May 10 '17 at 20:33