4 ms·

One of the first bugs I found in production code at my first job was caused by the dev using CHAR(n) in a temporary table, causing all values that went through

One of the first bugs I found in production code at my first job was caused by the dev using CHAR(n) in a temporary table, causing all values that went through it to be padded with spaces. I understand the need for length-limited character strings, but I love modern DBMSs letting you use STRING/TEXT/VARCHAR with no limit and no efficiency loss.

sgarland 3y ago
There are limits and efficiency hits, they’re just edge cases / minor. For MySQL, anyway: VARCHAR > 255 requires an extra byte as a length prefix. This isn’t a big deal for most people - even with a billion rows, you’ve only lost 1 GB of space (assuming you didn’t actually need the larger VARCHAR). Additionally, there is a maximum row limit of 2^16-1 bytes, and collation must be considered. The modern standard in MySQL is utf8mb4, so each character can require 4 bytes. If you’re using an older collation like utf8mb3 or latin1, you can squeeze more in, but when/if those get deprecated (utf8mb3 will supposedly be at some point), the collation change might push you over the edge. LONGTEXT can store 2^32-1 bytes, so even with utf8mb4 you can store 1 GiB in a single string. Please don’t do this. Finally, if your table has any BLOB (including TEXT) types, you may get various limitations depending on the version, like implicit temp tables being unable to be created in memory resulting in disk hits; indexing fun (if you need to index a TEXT column - no you don’t), and other quirks. Databases are fun.
- jeltz 3y ago
  In PostgreSQL on the other hand there is no efficiency hit. A varchar(n) is on disk and in memory the exact same thing as a text.
hobs 3y ago
MSSQL - MAX has a definitely performance loss, memory grants are often off the charts and storing things off page has a direct performance loss.
- adzm 3y ago
  MSSQL/SQL Server will only store [n]varchar(max) data off-page when it exceeds the page size though. It is the [n]text type that would traditionally always be off-page.
  - dspillett 3y ago
    The point at which the data is pushed off-page is much lower than page size, only relatively small [N]VARCHAR(MAX) values are kept directly in-page. This makes sense as a design choice: for a lot of data & well designed queries, except where a single long data value is the main point of each row in the table or you are otherwise almost always needing to read the long text values, you are often much better off having many rows per page (with the extra lookups into LOB space) rather than allowing large text columns to drop that to one or two rows per page. This can be detrimental to the benefits of compression if you use that compromise to save space in exchange for a CPU cost, because the off-page data is not compressed.
tracker1 3y ago
MS-SQL definitely has a few performance implications... you also have limitations in terms of indexes. It also doesn't help that there are subtle differences between the different databases in terms of how this is handled. Aside, I really wish that MS had simply had an updated behavior for TEXT, where it behaved like VARCHAR(MAX) under the covers, given the underlying database was updated to version N. Which would be slightly easier to deal with in practice and more similar to say PostgreSQL where that is effectively already the behavior. Aside: None of this accounts for proper Unicode (UTF-8) normalization for indexing/size. Which leaves it to the application to deal with in practice.

sgarland 3y ago

There are limits and efficiency hits, they’re just edge cases / minor. For MySQL, anyway: VARCHAR > 255 requires an extra byte as a length prefix. This isn’t a big deal for most people - even with a billion rows, you’ve only lost 1 GB of space (assuming you didn’t actually need the larger VARCHAR). Additionally, there is a maximum row limit of 2^16-1 bytes, and collation must be considered. The modern standard in MySQL is utf8mb4, so each character can require 4 bytes. If you’re using an older collation like utf8mb3 or latin1, you can squeeze more in, but when/if those get deprecated (utf8mb3 will supposedly be at some point), the collation change might push you over the edge. LONGTEXT can store 2^32-1 bytes, so even with utf8mb4 you can store 1 GiB in a single string. Please don’t do this. Finally, if your table has any BLOB (including TEXT) types, you may get various limitations depending on the version, like implicit temp tables being unable to be created in memory resulting in disk hits; indexing fun (if you need to index a TEXT column - no you don’t), and other quirks. Databases are fun.

jeltz 3y ago
In PostgreSQL on the other hand there is no efficiency hit. A varchar(n) is on disk and in memory the exact same thing as a text.

hobs 3y ago

MSSQL - MAX has a definitely performance loss, memory grants are often off the charts and storing things off page has a direct performance loss.

adzm 3y ago
MSSQL/SQL Server will only store [n]varchar(max) data off-page when it exceeds the page size though. It is the [n]text type that would traditionally always be off-page.
- dspillett 3y ago
  The point at which the data is pushed off-page is much lower than page size, only relatively small [N]VARCHAR(MAX) values are kept directly in-page. This makes sense as a design choice: for a lot of data & well designed queries, except where a single long data value is the main point of each row in the table or you are otherwise almost always needing to read the long text values, you are often much better off having many rows per page (with the extra lookups into LOB space) rather than allowing large text columns to drop that to one or two rows per page. This can be detrimental to the benefits of compression if you use that compromise to save space in exchange for a CPU cost, because the off-page data is not compressed.

tracker1 3y ago

MS-SQL definitely has a few performance implications... you also have limitations in terms of indexes. It also doesn't help that there are subtle differences between the different databases in terms of how this is handled. Aside, I really wish that MS had simply had an updated behavior for TEXT, where it behaved like VARCHAR(MAX) under the covers, given the underlying database was updated to version N. Which would be slightly easier to deal with in practice and more similar to say PostgreSQL where that is effectively already the behavior. Aside: None of this accounts for proper Unicode (UTF-8) normalization for indexing/size. Which leaves it to the application to deal with in practice.