DATETIME2 is the new data type introduced in SQL Server 2008
DATETIME2 provides more seconds precision & has larger date range, a larger default fractional precision. DATETIME is limited to 3 1/3 milliseconds, while DATETIME2 can be accurate down to 100ns
Advantages of DATETIME2
DATETIME2 has a date range of "0001 / 01 / 01" through "9999 / 12 / 31" while the DATETIME type only supports year 1753-9999
Larger range of values
Better Accuracy
Smaller storage space (if optional user-specified precision is specified)
Precision, scale
0 to 7 digits, with an accuracy of 100nanoseconds.
The default precision is 7 digits.
Storage Size
6 bytes for precision less than 3
7 bytes for precision 3 and 4.
All other precision require 8 bytes.
DATETIME2 with fractional seconds precision of 3 is same as DATETIME data type. And DATETIME2 (precision of 3) uses 7 bytes of storage instead of 8 byte
If you only need the date without time part use DATE
If you only need the time without date part use TIME
Using DATE & TIME data types instead of DATETIME2 in above situations you save the space
No comments:
Post a Comment