The size of dateTime2(0), dateTime2(1), dateTime2(2), dateTime2(3) use
the same amount of storage. (6 Bytes)
Would I be correct in saying that I might as well go with dateTime2(3)
and gain the benefit of the precision without any additional size
No, you misinterpreted the documentation. Note the doc states storage size 6 bytes for precisions less than 3 (emphasis mine). So a precision equal to 3 will require 7 bytes.
If you don't care about milliseconds,
datetime2(0) would be the proper data type and precision. The best practice is to specify the proper data type and precision based on the data stored as this will inherently provide optimal storage and efficiency. That being said, I would not expect a significant performance impact based on the specified datetime2 precision as long as the storage size is the same but I have not specifically tested that myself.
Application requirements will dictate what must be stored in the database when a greater precision is available in the source. For example, for an order entry time sourced from
SYSDATETIME(), users may not want 100 nanosecond precision. Again, choose the data type and precision according to requirements and you will generally get optimal performance without additional thought:
- date - you don't need time
- smalldatetime - you don't need seconds
- datetime2(0) - you don't need fractional seconds
- datetime2(1-7) - you need fractional seconds of the specified precision
Consider that storing a greater precision than required may also have a development cost. If one stores a time component with fractional seconds when only whole second precision is required, queries will still need to consider the fractional seconds to return the correct results. For example, with an app where the user selects a time range via a UI that only allows for whole seconds, the app code would need to account for the fractional seconds in the end time range value and adjust the user-supplied value accordingly (e.g.
WHERE OrderEntryTime BETWEEN '2017-01-11T08:00:00.00.00' AND '2017-01-11T08:59:59.99' or
WHERE OrderEntryTime >= '2017-01-11T08:00:00.00' AND OrderEntryTime < '2017-01-11T09:00:00.00'). This will add code complexity.