4 ms·
> If you know that the only possible values for a certain column are between 0 and 100,000 then you don’t need to slap a BIGINT data type for that column when a
by sehrope 13y ago
> If you know that the only possible values for a certain column are between 0 and 100,000 then you don’t need to slap a BIGINT data type for that column when a INT would do just fine.
What conceivable column could have 100K possible values but not more than 4B? Factoring in gaps in sequence numbers, wasted ids for testing, parallel generation of ids, etc, it's not that hard to crack the 32-bit boundary.
If you're dealing with something you know is of a fixed small size, for example surrogate keys for a constants table that is manually created by you (the app designer) then you can use an int (32-bit), short (16-bit), or even an single byte (8-bit). For anything else though just use 64-bit ids.
> what about those NCHAR(2000) columns that are storing mostly first and last names? How much extra overhead for those?
This makes no sense. Using VARCHAR fields the size is just a max. You should still pick a reasonable max but just because its defined as VARCHAR(256) doesn't mean it's stored as 256 bytes.
- tracker1 13y agoCHAR/NCHAR are fixed length, VARCHAR/NVARCHAR are dynamic length... Usually the biggest restrictions to varchar/nvarchar are indexing limits.. generally my indexed fields for n/varchar will be 100 (email, name, etc) non-indexed, xml hashes, json will be NVARCHAR(MAX)/NTEXT when they aren't indexed.
- sehrope 13y agoYes but I see no point in using fixed length string fields. Double so for the examples the article gives (first and last names). All the times I've encountered them has been with legacy systems ands it's been a universal pain. All your front end code ends up doing TRIM(...) to clean them up the extra padding. Modern RDBMSs all handle variable length fields efficiently so it's a waste of programmer time to have to deal with CHAR/NCHAR fields.