What are the potential risks of increasing column size in SQL?

Question

Suppose I have a column called ShortDescription in a table called Ticket.

ShortDescription varchar (16) NOT NULL

Now, suppose I increase the size like this -

alter table Ticket modify ShortDescription varchar (32) NOT NULL;

What are the potential risks of doing this? One potential risk is that if some other applications have statically set any of their fields to size 16 based on the previous size of ShortDescription, then those applications may not behave correctly with data of greater size.

Answer 1

SQL is a query language, not a specific DB implementation, so your mileage may vary, but ...

Assuming 'SQL' means a MySQL DB on the DB site you've nothing to worry about on the storage and performance outside the fact if you store a bunch of 32byte stings you'll use more memory and disk working with them, but if what you actually store in them is 16 byte characters, the convertion to VARCHAR(32) is a wash.

Within MySQL, VARCHAR there is no impact (assuming you keep the NOT NULL). If the column is used in a composite primary key, you may hit a size limit, but otherwise all varchar entries only take size of data + 1 byte to store.

If the column is referenced as a foriegn key in some other table, you'll need to grow that column as well to VARCHAR(32) or may experience truncation of the extra 16 characters if you try to jam at 32 character string into a 16 character column.

If not MySQL, implementations could be different across DB technologies. However, VARCHAR implementations tend to be similar, using just the size of the stored data and then a constant amount to signify end of data. Hence why usually you have options bewteen a static CHAR and a dynamic VARCHAR type in many DB systems.

As you noted in your post, external systems relying on static data size have to be considered.

Note: please excuse the above fast and free swapping of terms byte and character, I'm assuming UTF8 or ASCII. If you're using some multibyte encoding, substitute approriately.