June 28, 2012 at 10:02 am
Hi
I need to chaange the properties of a colunn that appears in more than 25 tables from (varchar) to (numeric). What is the best way to do it?
June 28, 2012 at 6:05 pm
I see you're relatively new to SSC, welcome. At first glance your question has a simple answer, but it is not a simple affair. You can change a column's data type using the ALTER TABLE...ALTER COLUMN command so issuing 25 of those, one for each table and column, will get the job done. That said, and don't let the rest of this post scare you off, but there is a lot more to consider when making a change like that.
1. Is all the data convertable to NUMERIC(n,n)? Have you checked to make sure the data is clean and can be cast to the NUMERIC type?
2. What is the fill factor on all indexes the column participates in? Are any of the tables heaps (i.e. a table with no clustered index)? NUMERIC is a fixed width data type, VARCHAR is not. So, changing a column from a variable-width data type to a fixed-width data type can generate page splits in clustered tables or forwarding pointers in heaps. If you have an index maintenance job setup for your database you may want to plan on running it after making the change. You might also look into rebuilding any heaps using ALTER TABLE REBUILD depending on how many forward pointers are generated from the operation.
3. How much data is in these tables (rows and total size)? An operation like this can take a long time for the reason stated in item 2, or simply because of high data volumes.
4. What else is accessing this data? For some data consumers, SSIS included, VARCHAR and NUMERIC are not transparently interchangeable. Have you looked into any apps or people that are accessing this data to make sure they can keep pace with this change?
I am sure there are other considerations not coming to mind. Hopefully others will chime in.
First and foremost, do you have an environment representative of production to try this in? Preferably a place where you can also allow any data consumers to test as well, before making the change to your live database.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 29, 2012 at 2:43 pm
Thanks opc.three that helped alot.
June 29, 2012 at 3:39 pm
jdbrown239 (6/29/2012)
Thanks opc.three that helped alot.
You're welcome, have a good weekend.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply