Performance difference for STRING(MAX)?

Question

Is there a performance penalty (or improvement) for using STRING(MAX) instead of some fixed limit like STRING(256)?

Answer 1

Nope. STRING(MAX) is treated exactly the same as strings of limited length under-the-hood. Same applies for BYTES(MAX). So there is no performance difference.

The main reason to use a fixed limit is if there are logical constraints you want to enforce in your schema. For example: if you are using a STRING to store 2-letter country codes, then you might want to using STRING(2).

Note that, according to the docs, you can always change the length limit for a string, except with one caveat:

• Supported Schema Updates: Increase or decrease the length limit for a STRING or BYTES type (including to MAX), unless it is a primary key column inherited by one or more child tables.