想要更改表定义但数据量很大

This is the continual question from this previous question I've asked: Change Database Column Design in SQL Server

这是我之前提出的问题的持续问题:在SQL Server中更改数据库列设计

The table I'm trying to alter the table definition is quite large (4137631 rows) so Management Studio says the table would be inaccessible during the process of converting and it could take some time to finish.

我正在尝试更改表定义的表非常大(4137631行),因此Management Studio表示在转换过程中该表将无法访问,并且可能需要一些时间才能完成。

So I need advices on how to handle the situation.

所以我需要有关如何处理这种情况的建议。

Should I wait for the after office hour to make changes?

我应该等待下班后时间进行更改吗?

Any ideas how long would it take?

任何想法需要多长时间?

Or is there any other faster way to change the table definition (varchar to nvarchar)

或者是否有任何其他更快的方法来更改表定义(varchar到nvarchar)

Thanks in advance.

提前致谢。

5 个解决方案

#1

You should schedule a job until the database is not being used at all. Failing that, take the database offline first.

您应该安排作业,直到根本没有使用数据库。如果做不到这一点,请先使数据库脱机。
Noone will be able to tell you precisely.

没有人能够准确地告诉你。
ALTER TABLE is your only option, unless you feel like copying the entire table first (in which case you'll run into problems with referential integrity ANYWAY, not recommended).

ALTER TABLE是您唯一的选择,除非您想首先复制整个表格(在这种情况下,您将遇到参考完整性问题,不建议使用)。

#2

is the column being changed is part of any index? If not

被更改的列是否是任何索引的一部分?如果不

Add a new column ( nvarchar) to the database

将新列(nvarchar)添加到数据库

update the column with data from original column

使用原始列中的数据更新列

drop the original column

删除原始列

rename the new column

重命名新列

I prefer doing it as script.You can try above steps in your test system . It should be ideally faster than any other approach.

我更喜欢将其作为脚本。您可以在测试系统中尝试上述步骤。理想情况下,它应该比任何其他方法更快。

#3

Make your script with alter table / alter column: http://msdn.microsoft.com/en-us/library/ms190273.aspx

使用alter table / alter column创建脚本:http://msdn.microsoft.com/en-us/library/ms190273.aspx

But make a test table with the same structure first, make your script as a stored procedure then, test is first on this table and if it works perfectly, then schedule the SP for running once in the night. No user will be harmed : )

但是首先制作一个具有相同结构的测试表,然后将您的脚本作为存储过程,然后在此表上首先测试,如果它完美运行,则安排SP在夜间运行一次。没有用户会受到伤害:)

#4

best practice is to create another database as Test and restore a copy of the production database to test and try to run the script to alter the table and from that point you may decide on how things go after the alteration.

最佳实践是创建另一个数据库作为测试并恢复生产数据库的副本以进行测试并尝试运行脚本来更改表,从那时起您可以决定更改后的事情。

Rhian A.

#5

The Answer to your Questions
1) Definitely wait after hours for this , it is a maintenance activity and should not be run during the working hours
2) How long it will take depends on your Hardware ,Network Speed and Amount of Data in the table. Number of Rows is not a good measure of the amount of data. I can have only 3 columns all integers and as many rows as you said. If you know what i mean.
3) I have put my comments in that Question.

您的问题的答案1)绝对等待数小时后,这是一项维护活动,不应在工作时间内运行2)需要多长时间取决于表中的硬件,网络速度和数据量。行数不是衡量数据量的好方法。我可以只有3列所有整数和你说的行数。如果你明白我的意思。 3)我已将我的评论放在该问题中。

#1

You should schedule a job until the database is not being used at all. Failing that, take the database offline first.

您应该安排作业,直到根本没有使用数据库。如果做不到这一点,请先使数据库脱机。
Noone will be able to tell you precisely.

没有人能够准确地告诉你。
ALTER TABLE is your only option, unless you feel like copying the entire table first (in which case you'll run into problems with referential integrity ANYWAY, not recommended).

ALTER TABLE是您唯一的选择,除非您想首先复制整个表格(在这种情况下,您将遇到参考完整性问题,不建议使用)。

#2