I agree that there are no real benefits in terms of storage space as long as you are comparing DECIMAL(9, 0) vs INT or DECIMAL(18, 0) vs BIGINT. (Within a single byte.)
In terms of processing, like @Andriy says the DECIMAL will naturally divide into a type that doesn't lose the fractional part, if that's important to you.
On the other hand, working with native INT types is much faster from a numerical standpoint if you are doing a lot of SUM()s or comparisons (such as searching on the values) as they are pipelined more efficiently by the CPU. An int comparison is two assembly opcodes (MOV, CMP) but any decimal comparison will be many, many more.
The size in and of itself is what you will need to appropriately determine when setting and growing. This is all part of properly sizing your database.
But you need to be careful with growing your files at such a small increment. If you are growing your files often, you are causing SQL Server to have to do this relatively expensive operation. Take, for instance, growing of the transaction log. When this happens all write activity will be impeded. Another thing about that is if you grow your transaction log at a small interval and it grows often, you will end up with a large amount of VLFs that are small in size.
Instant File Initialization is a great approach, as it bypasses the zero-initialization of the additional file (or file allocation). This shaves off a lot of the overhead and performance impact, but this is only possible with data files, not log files.
As you can see, there are a handful of things to consider depending on your environment and how much your database grows. Size your database appropriately and find the right growth increment.
Note, autogrowth should be reserved for emergency situations and it is recommended to manually grow your files if at all possible. This gives you full control over all of the points above, as well as allowing you to determine when this operation occurs.
As for your numbers, "space available" is just that: The free space that you have for additional data until growth will need to happen. One strategy is to get notified when you reach a certain threshold of used space so that you can schedule and kick off growth of the appropriate file(s).
Best Answer
Using
bigintcompared tointhas, at least, these potential performance drawbacks:That being said, how much practical impact these things have for you is very dependent on your specific environment and workload.
Note: the increases in disk and RAM usage issues can be mitigated by using row compression, at the cost of increased CPU usage. As servers often have more CPU overhead than RAM, this is generally a good tradeoff (thanks to Andy for this reminder!)