CODEWITHSUNDEEP
sqlsql-serverdatabase-designsqldatatypes
Ye Myat Aung
Ye Myat Aung

Reputation: 1853

Want to change table definition but amount of data is large

This is the continual question from this previous question I've asked: Change Database Column Design in 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.

So I need advices on how to handle the situation.

Thanks in advance.

Upvotes: 1

Views: 2504

Answers (5)

bhupendra patel
bhupendra patel

Reputation: 3179

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.

Upvotes: 0

Las Ten
Las Ten

Reputation: 1175

Make your script with 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 : )

Upvotes: 1

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

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.

Upvotes: 0

Chandra
Chandra

Reputation: 333

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

  1. Add a new column ( nvarchar) to the database
  2. update the column with data from original column
  3. drop the original column
  4. 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.

Upvotes: 1

hkf
hkf

Reputation: 4520

  • 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).

Upvotes: 1

Related Questions