Home / server / Questions / 836573
Accepted
Andreas
Asked: 2017-03-07 05:15:40 +0800 CST

SQL change column datatype in production

  • 772

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.

sql-server

1 Answers

  1. Best Answer

    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);
GO
UPDATE [dbo].[Table] SET ColumnTemp = Column
GO
ALTER TABLE [dbo].[Table] DROP COLUMN Column
GO
EXEC sp_rename 'dbo.Table.ColumnTemp', 'Column', 'COLUMN'
GO
    • 0