Converting a Numeric Field to a Numeric Field
When we create the first version of an application, we're prescient enough to always know the exact functional and storage needs of the application, no? I recently ran in to a bit of a problem caused by some poor planning on my part (or maybe it was the guy who wrote the original version of the app before me), and ran up against an unpleasant datatype conversion issue in SQL Server.
There's a field in the database for this particular app which stores relatively small numeric values — point values for each question in a quiz. This field is currently defined as a NUMERIC(4,2) in SQL Server. For some of you, this may have immediately raised a red flag. Why is this field limited to such a small range of numbers? Sometimes my long experience developing Web-based applications gets the better of me, and I concern myself with the limits of storage from an old-school "an 8GB hard drive costs $400!" point of view. And there are reasonable arguments to be made for limiting the size of fields within a database. However, my original reasoning was something like "Well they're not going to need values above 1000 nor decimal accuracy past two places, so why bother?"
You can see where this is going, right?
Now I have to support decimal accuracy to three places (and, sooner, or later, someone will ask me for four places) because in the world of scores for quizzes and exams, there's a big difference between 3.125 and 3.13.
So my problem was this: how to change the definition of the database field from NUMERIC(4,2) to a more standard NUMERIC(18,5)? Well you can't just fire up SQL Server Management Studio, right click the table, select "Design" and change the datatype. You'll get an internal conversion error from the database engine.
The next approach was to create a new column with the correct datatype and copy the contents of the old column in to the new one, drop the old column, and rename the new column with the old column name. In SQL, it's pretty trivial to copy one column to another. You just run:
SET column1 = column2
However, when copying from a NUMERIC to a NUMERIC field, the database engine again throws the internal conversion error.
In this article from the SQL Server docs on CAST and CONVERT, there's a figure about 1/2 way down that shows which datatype conversions are supported in SQL Server. You'll notice that NUMERIC to NUMERIC is supported but it "Requires explicit CAST to prevent loss of precision or scale that might occur in an implicit conversion." So all I have to do is CAST() the origin field and we'll be OK, right?
Nope. Yet more internal conversion errors.
What I ended up doing was this, and I probably should have done everything in the database, but I was done wasting time and knew I could get this one time conversion done quickly using ColdFusion. I simply <cfquery>ied the database to pull all values from the original column, but CASTing the values as FLOATs. I then <cfloop>ed over that query and UPDATEd the table with the new values. SQL Server had no issues in going from FLOAT to NUMERIC(18,5). I was then able to drop the old column, rename the new column, and everything was good again.
I'm sure that if my SQL-Fu was better, I could have created a temp table, copied the data in there, done the CAST to FLOAT in the copy, then pulled from that temp table and updated the new column in the original table with the new values from the temp table. If anyone would like to provide me some samples to get started, or has alternate suggestions, I'm more than willing to give them a go.


