On Sun, Feb 11, 2024 at 11:54 AM veem v <veema0000@xxxxxxxxx> wrote:
[snip]
When you said "you would normally prefer those over numeric " I was thinking the opposite. As you mentioned integer is a fixed length data type and will occupy 4 bytes whether you store 15 or 99999999.But in case of variable length type like Number or numeric , it will resize itself based on the actual data, So is there any downside of going with the variable length data type like Numeric,
Consider a table with a bunch of NUMERIC fields. One of those records has small values (aka three bytes). It fits neatly in 2KiB.
And then you update all those NUMERIC fields to big numbers that take 15 bytes. Suddenly (or eventually, if you update them at different times), the record does not fit in 2KiB, and so must be moved to its own.page. That causes extra IO.
Varchar type always for defining the data elements?
Internally, all character-type fields are stored as TEXT. CHAR and VARCHAR(XX)'s only purposes are SQL-compliance and length-limitation.
And length-limitation is "just" a legacy carried forward from the card punch days.
