SQL - is BIGINT datatype always 8 bytes?

Question

I know BIGINT can store a 8byte numbers. I want to store numbers with length 12 (Fixed length). So using INT is impossible.

If I store a number like153159842655, How much storage it takes ? Is it always 8byte or depends on number ?

Second question is that is increasing or decreasing length of number affects on performance (Indexing that numbers, Selecting ...) ? This table can have billions records ! I use InnoDB

Check docs for BigInt https://mariadb.com/kb/en/mariadb/bigint/ — Gabriel Molina, Aug 03 '17 at 15:28
If MySQL https://dev.mysql.com/doc/refman/5.5/en/integer-types.html — Gabriel Molina, Aug 03 '17 at 15:28

score 0 · Answer 1 · answered Aug 03 '17 at 15:40

0

Regarding storage of a BigInt type column check this answer from dba exchange (is for SQLServer, but similar number would be expected by other RDBMS): https://dba.stackexchange.com/questions/98433/storage-size-of-a-bigint-table#98435

Regarding performance I think you shouldn't be worried about that in numeric data types. All databases are well suited to work with millions of records, and selecting data will be faster when using and index. You can find a comparison of different numeric datatypes performance in Oracle here: https://oracle-base.com/articles/misc/performance-of-numeric-data-types-in-plsql

Of course you need to know exactly what kind of data will you be storing in order to choose the best datatype (the one that fits your needs).

answered Aug 03 '17 at 15:40

Gabriel Molina

539
3
14

I want to know storage space for 4 length number vs 20 length number in a BIGINT column – AliN11 Aug 03 '17 at 15:50
All have the same 8 bytes. – Gabriel Molina Aug 03 '17 at 15:57
Even if I store 0 (Zero) ? – AliN11 Aug 03 '17 at 16:00
Yes. A word of 8 bytes represent one number, including zero. The only difference would be a null value. – Gabriel Molina Aug 03 '17 at 16:05

SQL - is BIGINT datatype always 8 bytes?

1 Answers1