It has been a while since I wrote a blog post for TSQL Tuesday and there is no better time then now following on from Brent Ozar’s Invite about our favourite data types in SQL server.

No I am not made for MONEY or feverish for FLOAT or crazy for CHAR. This post is about INT moving to BIGINT. Lets do some theory first. INT and BIGINT typically used where you need to use integer data. Each type has its use case but the range and storage is what should be driving your decision making when designing tables. Below shows the difference between the two.

Of course you should use INT where possible (especially for clustered index keys – but that’s a different topic to this one) but always think of the future. Many years ago designers built an audit table for changes to a datawarehouse based table, guess what its key column was based on an INT.

7 years later the table grew beyond the INT range -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647). They never though that through the rate of change that this would ever become out of range.

Processes started to fail, data become stale and reporting was down. This was the job for the DBAs to move the datasets and processes to a different table now with BIGINT hence with a range of: 2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807).

Safe to say that there is plenty of room for growth and this is how BIGINT saved that day.