NVARCHAR(MAX) to VARCHAR(MAX)

Question

NVARCHAR(MAX) to VARCHAR(MAX)

Super Cat

SSCertifiable

Points: 7332
More actions
August 9, 2023 at 2:33 pm

#4256880

Quick and simple. Converting a column from NVAR(MAX) to VAR(MAX) has drastically increased the data stored in the column.
Table size in MB is drastically higher after the conversion. There are extra GBs of data after conversion.
Any explanations?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101091 More actions · Answer 1

When you ALTER a column, SQL creates a new column to store the data, it doesn't re-use the existing space for the new column. You'll need to do some type of rebuild on the original table/clustered index to clean up/remove the old data.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 2

Super Cat wrote:

Quick and simple. Converting a column from NVAR(MAX) to VAR(MAX) has drastically increased the data stored in the column.
Table size in MB is drastically higher after the conversion. There are extra GBs of data after conversion.
Any explanations?

Yes... as of 2005, MS made the (IMHO) grand mistake of defaulting LOBs to being "In-Row". A lot of your NVARCHAR() stuff was "Out-of-Row" because of its size. When you cut that size in half by converting them to VARCHAR() a lot of those LOBs suddenly become "small enough" to fit "In-Row". That results in a much smaller number of rows per page and, frequently, also results in a very large number of "Trapped Short Rows" where, depending on the columns in the table, can result in many pages only holding 1 small row wasting sometimes as much as 98 % of the page. This isn't temporary either. Neither a REBUILD nor a REORGANIZE will fix it. You'll have to set the table option to force LOBs out of row and then update every LOB to itself to force the LOBs to move out of row and then you'll need to rebuild the clustered index on the table to recover the wasted space.

Having those LOBs go "in-row" will also serious reduce performance of anything and every thing that depends on range scans for performance and can cause extreme page splitting issues if you update a NULL or small LOB to something larger.

There are other issues at hand but that will get you out of the

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Super Cat SSCertifiable Points: 7332 More actions · Answer 3

Super Cat

SSCertifiable

Points: 7332

August 9, 2023 at 2:55 pm

#4256886

Thanks Both.

Super Cat SSCertifiable Points: 7332 More actions · Answer 4

I checked and the increase is strangely in the LOB data. Why would the LOB data increase?

Super Cat SSCertifiable Points: 7332 More actions · Answer 5

Also how do I check if the table has had any options set for SP_TABLEOPTIONS such as 'LARGE VALUE TYPES OUT OF ROW'. I doubt this would be the cause but I would like to see if any none default options have been set for the table.

ratbak Ten Centuries Points: 1358 More actions · Answer 6

To use out-of-row for LOBs:

EXEC sp_tableoption 'SchemaName.TableName', 'large value types out of row', 1;

To release unused space:

DBCC CLEANTABLE (MyDbName,"MyTableName ", BatchSize)

(BatchSize is optional)

If that doesn't work -- if is a heap, you might need to use

ALTER TABLE [TABLE_NAME] REBUILD

ScottPletcher SSC Guru Points: 101091 More actions · Answer 7

Super Cat wrote:

Also how do I check if the table has had any options set for SP_TABLEOPTIONS such as 'LARGE VALUE TYPES OUT OF ROW'. I doubt this would be the cause but I would like to see if any none default options have been set for the table.

You can check sys.tables, column "large_value_types_out_of_row" for that. If it is 1 for a table, it has been set; if 0, it has not.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 8

ratbak wrote:

To use out-of-row for LOBs:

EXEC sp_tableoption 'SchemaName.TableName', 'large value types out of row', 1;

That won't move existing LOBs. You have to update each row to get the LOBs to move out of row after making that setting. Then, don't even bother with CLEANTABLE thing. Rebuild the CI and be done with it.

The only time you need to do the CLEANTABLE thing is if you drop a VARCHAR or NVARCHAR column.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)