Alter table with image column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Alter table with image column

Author

Topic

omniman
Starting Member

4 Posts

Posted - 2010-05-07 : 10:06:12

Hello,

I have a table with an image column that holds binary data. There are about 100,000 rows in the table and it is roughly 60 gigs in size. There is a varchar(20) column in the table to hold the file name and I would like to increase that to varchar(50). To my understanding, that can't be done purely with an ALTER statement; but needs to create a new table, import the data, delete the old table, and rename the new table. When I tried this it was taking way too long because of the huge table size. I did this over night, but after almost 2 1/2 hours of the script running I was halfway though our outage window and essentially hit the breaking point so I had to cancel and rollback the transaction so we would be up and running again after the 5 hour window.

Is this really the best method to go about this or is there another way? As a side note, we are working on an archiving strategy to get that table size down but we really need to increase the column length before we'll be able to finish the archiving.

Thanks,
Ryan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-05-07 : 11:50:17

You might try doing the procedure in the script below, since it is a fairly small number of rows. You should restore the DB to a new database, and test it to see how long it takes.

It is not entirely transparent, since the FileName column will be in a new ordinal position, but I'm sure none of your developers have made the mistake of using SELECT *.


-- Add new column
alter table dbo.MyTable
add MyNewFilename varchar(50) null

-- Set new column to values of old column
update dbo.MyTable
set MyNewFilename = MyFilename

-- Rename old column
exec sp_rename
	@objname = 'dbo.MyTable.MyFilename',
	@newname = 'MyOldFilename',
	@objtype = 'column'

-- Rename new column to former name of old column
exec sp_rename
	@objname = 'dbo.MyTable.MyNewFilename',
	@newname = 'MyFilename',
	@objtype = 'column'

-- Remove old column
alter table dbo.MyTable
drop column MyOldFilename

CODO ERGO SUM

omniman
Starting Member

4 Posts

Posted - 2010-05-14 : 10:44:00

I haven't had a chance to try this out yet but it looks like it will work great so I just wanted to say thanks for the help.

Ryan

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-05-14 : 16:43:50

You are welcome.

Like I said before, you should test this on a copy of the database first.

Let us know how it works.

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2010-05-15 : 04:22:00

That would be my approach too (although I agree with MVJ that it needs testing!)

Only possible issue with this is that it leaves a "gap" in the Column-Sequence-IDs. I doubt this is a problem for anything, but I have had 3rd party stuff that coughs when that happens. e.g. when the table has been created (from scratch) on another database and some data comparison tools are not matching up the columns because their Column-Sequence-IDs are different.

Thus if opportunity presents itself, in the future, I would copy-to-new-table to get sequentially numebered IDs again. Just-in-case + Belt-and-braces!

omniman
Starting Member

4 Posts

Posted - 2011-03-13 : 13:02:25

OK, I know this is WAY late but just wanted to say thanks again! For various reasons this was sidelined and I just now made this change but am happy to report that it worked without a glitch!

Thank you,
Ryan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources