Best/most efficient way to convert Nvarchar(max) to nvarchar (30)
I was wondering if anyone could give me a pointer with the problem. I assume others have experienced this before and was wondering what is the most efficient.
I have production data that is taking rather long to process, mainly because we haven't set indexes on an ID field.
The ID field is currently a nvarchar(max), so it can't be indexed. This ID is never longer than 30 characters, and I would therefore like to change the data type to nvarchar(30). However, I've noticed that using a statement such as:
ALTER TABLE [dbo].[Table] ALTER COLUMN [Column1] NVARCHAR ( 30 ) NOT NULL
Takes a significant amount of time for the smaller tables already. I'm worried it might not even work for the larger tables, as the DiskIO is incredibly high for the smaller tables already.
Would the following be faster and could it work?
- Create a new column with the nvarchar(30) data type.
- Then copy the data from the nvarchar(max) column to the nvarchar(30) column.
- Then remove the nvarchar(max) column.
- Then rename the nvarchar(30) column to the name of the nvarchar(max) column?
Does anyone have any suggestions as to what might be even better?
Any pointers/help would be appreciated!作者: Mark 的来源 发布者： 2017 年 12 月 27 日
We solve a similar issue by creating an empty new column (NULL) and updating in batches
作者: Horaciux 发布者: 2017 年 12 月 27 日
update top (5000) myTable --parentheses are mandatory, set a small number to avoid transaction log issues set newCol = oldCol -- or left(oldCol,30) not sure your data where newCol is null go 10000 --runs 10,000 times, set accordingly to your rows amount, this covers 50 million rows(5,000x10,000)