OK, let’s do a quick math recap:
-
INT is 32-bit and gives you basically 4 billion values – if you only count the values larger than zero, it’s still 2 billion. Do you have this many employees? Customers? Products in stock? Orders in the lifetime of your company? REALLY?
-
BIGINT goes way way way beyond that. Do you REALLY need that?? REALLY?? If you’re an astronomer, or into particle physics – maybe. An average Line of Business user? I strongly doubt it
Imagine you have a table with – say – 10 million rows (orders for your company). Let’s say, you have an Orders table, and that OrderID which you made a BIGINT is referenced by 5 other tables, and used in 5 non-clustered indices on your Orders table – not overdone, I think, right?
10 million rows, by 5 tables plus 5 non-clustered indices, that’s 100 million instances where you are using 8 bytes each instead of 4 bytes – 400 million bytes = 400 MB. A total waste… you’ll need more data and index pages, your SQL Server will have to read more pages from disk and cache more pages…. that’s not beneficial for your performance – plain and simple.
PLUS: What most programmer’s don’t think about: yes, disk space it dirt cheap. But that wasted space is also relevant in your SQL Server RAM memory and your database cache – and that space is not dirt cheap!
So to make a very long post short: use the smallest type of INT that really suits your need; if you have 10-20 distinct values to handle – use TINYINT. If you need an order table, I believe INT should be PLENTY ENOUGH – BIGINT is only a waste of space.
Plus: should any of your tables really ever get close to reaching 2 or 4 billion rows, you’ll still have plenty of time to upgrade your table to a BIGINT ID, if that’s really needed…….