March 7, 2008 at 6:02 pm
Hi - I just had a very scary moment and now could use some advice.
I have a table with two zip codes. They were defined as length of 5. Now we want to go to 9 characters.
I thought I could just modify the columns. I have about 4 million records. These two fields are each part of an index.
I first got a time out error expanding one of the zip codes and did a Google and found out how to increase the time out value and increased it to 3000 from 30. Then it crashed again and I got a bunch of really sickening errors and actually the table first was locked and then all the tables in the database vanished.
While I was about to panic and was calling up our network person to inquire about last night's backup, all the tables magically reappeared. All the data is safe. And I am feeling grateful.
The interesting thing is that moments earlier I added a field to another table that had over 17 million records and that worked in a second.
Doing some googling around it seems that if I use the management studio GUI it creates a duplicate table, writes to it, deletes the old one, and renames the new one. That is why it is taking so long. Why did this not happen when adding a field to the other table?
Anyway,..
Now I am reading about Alter Table and am wondering what the steps are to accomplish this.
Here is my guess: drop both indexes, do an alter table, create both indexes. Is this safe?
I don't want to lose the data in these fields. I just want the ability to add a 9 character zip code going forward.
I am a little nervous right now and won't try this again until next week. And will try it on Northwind first.
Any tips would be greatly appreciated.
March 8, 2008 at 9:16 am
Are you using char(nchar) or varchar(nvarchar) because, especially when using fixed length fields SQL Server has to expand the rows which is probably going to cause page splits and, if an indexed column, it is re-organizing the index as well. I would think that dropping the indexes would work, but then you have the time to re-create the indexes. Another way to do this is to create a New table, insert the existing data into it, drop the old table, and rename the new table to the old table name. This is how many of the Guru's on this site recommend handling this type of issue.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 8, 2008 at 2:21 pm
Carolyn Stern (3/7/2008)
Doing some googling around it seems that if I use the management studio GUI it creates a duplicate table, writes to it, deletes the old one, and renames the new one. That is why it is taking so long. Why did this not happen when adding a field to the other table?
Basically, because the ALTER COLUMN clause has many more restrictions on it than the ADD (column) clause does.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply