Recently I was working with a customer that is trying to smooth out their database development process. They noted that a lot of changes tend to take a significant amount of time and impact their availability. They came to Redgate to see if Flyway might be a way to improve the effectiveness of their internal groups that build, deploy, and manage their database software.

We can help, but often when I get called to help architect things, we are trying to decide if the customer is a good fit for our solutions. Since we work on a subscription model, we want to be sure customers are happy and get value from their decision. Otherwise they have a poor experience and don't renew. This might be because they aren't ready, or it might be that the question wasn't considered of whether our solution fits their environment well. In any case, I usually dig into the goals and challenges they've faced with their current process.

In this case, they found that developers often changed data types of columns to better match the data being stored. That can be a disruptive change, and while Flyway does some amazing things, the software is still bound by the rules of making changes on the platform. In this case, SQL Server changes can sometimes be metadata-only changes, which are fast. Sometimes this isn't the case.

After the call, I started to wonder how often people change data types? I've rarely done this, and honestly, I've used a zero-downtime pattern a few times to add a new column, move data, use triggers, and then when I'm sure all data is moved, drop the old column. Changing types in place seems like a bad idea most of the time.

Sometimes it's not a big deal, but I prefer to avoid any potential problems if I can.

That got me wondering. How often do you change data types in existing tables/columns? Is this because of poor data modeling? Changing requirements? Or are you lucky enough to avoid this?

Or maybe you're unlucky in that you can't change those poorly chosen types.