To know the difference between SQL Server Datetime Vs DateTime2 you should go through the answer thoroughly. 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 costs? 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 needs will dictate what must be stored in the database when 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 for new development 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
- datetimeoffset(0-7) - you need date and time with time zone awareness
- time(0-7) - you need time only (no date) with fractional seconds of the specified precision
Although datetime2 is most appropriate for new development as listed above, one may sometimes need to use DateTime (fixed precision 3 with 1/300 fractional seconds accuracy) instead for compatibility with legacy DateTime applications, thus avoiding implicit conversions and unexpected comparison behavior, but at the expense of fractional second accuracy and increased storage.
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.
