expand column size
-
Hi SQL GURUS,
I have a large table with about 50 mil. rows and about 60 columns.
I want to expand 20 columns from 20 to 100 length ( all varchar).
I checked the ansi_padding is off. How can I do this? This is a production database and very busy. Please help. Thanks!
Robert
-
alter table <TableName>
alter column <ColName> varchar(100)
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
If your shop is 24/7 you are going to have to implement the sliding window view method. If not wait for low activity and do a recommended above.
hth
* Noel -
How does the sliding window method work?
-
Sorry I was too vage.
1. you create table with same structure + new altered columns (target)
2. a second table with just one column (PK) [slider]
3. Create a view that is just the union of both tables
4. RENAME the source table to something else and RENAME the view to the source table name (these two renames are where all the heat is taken ) and appropriate low activity in the system is required.
5. With instead of triggers on the view handle the DML Inserts go to the target, Deletes goes to both (limited by the slider) and Updates do the same that deletes does
6. Through transactions ( in a job)
-copy from source to target
- delete from source
-update the slider PKey
7. At the end Drop view, Slider and Triggers and rename target to source (same conditions as on step 4)
This method was once published by Dr. Tom Moreau and all credit should go to him
HTH
* Noel -
I'm not sure exactly what Noel was referring to but I assume it's something like creating a new table with the proper column lengths, import the data from the original table, then you can drop the original table and rename the new table. This can work in some cases, but it is a pain if you have any foreign key constraints (and you should) and it really isn't necessary in this case. Since the column is varchar, no additional space is actually allocated. it is basically just a meta data operation, and it will happen more or less instantaneously.
If you alter a column to a fixed width, then this sort of method is vital in a 24/7 operation.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
You are right but this method I had mostly used to add columns
* Noel -
Thank you very much Guys.
All your suggestions work well.
Robert
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply