I need to alter some columns from nText to Nvarchar(max) on a active database with a simple query like

ALTER TABLE [dbo].[Table] ALTER COLUMN [Column] nvarchar(max);

But my question is, would this be safe to do on database in production? The tables differs from 50 rows to 2m rows. And to answer that question, I think my question should be:

What is the SQL-server doing under the hood when you change a Ntext datatype column to a Nvarchar(max)?

I have tried to find reading material on this, but without any luck.


This is a table rebuild because of how they are stored internally. However, 2 million rows is not too bad so it make only take a few minutes

I would consider doing it differently though to allow batching and ensure all data is converted OK.

ALTER TABLE [dbo].[Table] ADD [ColumnTemp] nvarchar(max);
UPDATE [dbo].[Table] SET ColumnTemp = Column
ALTER TABLE [dbo].[Table] DROP COLUMN Column
EXEC sp_rename 'dbo.Table.ColumnTemp', 'Column', 'COLUMN'
| improve this answer | |

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.