CHAR() vs. VARCHAR() – Predictable Failure

Create a table with 8 CHAR(1000) Fields, and save it. This isn’t an article about normalization, so you 3rd Normal Freaks just bear with me a moment… Just focus on the field types.

Notice that you can save this table (no asterisk by the name on the page tab – It saved just fine).

Now add one more char(1000) field and attempt to save it again.

You can’t. As most of you probably expected ahead of time. I hear you saying “Uhm… thanks for pointing out the obvious, Kirb!”

Well, here’s the point I’m getting at. Change all the fields to VARchar(1000). And add a few more, even. NOW try to save the table.

It saves with nary a complaint. Some of you are still yawning, I know. But think about what this means.

If this table’s data can fail (ie, if you are ever going to run into a maximum size overflow), the CHAR() version will tell you immediately. The VARCHAR() version might not happen until production time. It might not happen until years after the app is delivered and running.

The downside, of course is that char() allocates all the storage for the maximum value size immediately, and stores it. VarChar() might be nice for reducing storage requirements, but it does so at the cost of unpredictable failure.

A case of “choose your poison”.

Advertisements

About combatdba

I'm a production DBA at a terabyte-class SQL Server Shop
This entry was posted in Procedures. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s