13

According to this article https://msdn.microsoft.com/en-us/library/bb677335.aspx.

Storage size 6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes.

The size of dateTime2(0), dateTime2(1), dateTime2(2)use the same amount of storage. (6 Bytes)

Would I be correct in saying that I might as well go with dateTime2(2) and gain the benefit of the precision without any additional size costs.

Please note:

  • This column is indexed with the PK to form a compound Clustered Index. (used for table partitioning)
  • I do-not care about milliseconds

Would 'dateTime2(0)' be more cpu efficient when being used in an where query or when seeking through an index.

This is a massive table so the smallest optimization will make a big difference.

22

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 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.