We recently had a problem at work where we inserted a data in a very short amount of time. But then we noticed something strange with the inserted data, because after querying a lot of insertion times where the same but we were (kinda) sure that it shouldn’t be the same. This post will explain why that was and what to do about it.
So to get started we need to create a table which can hold our data
CREATE TABLE [dbo].[Table_DateTime]( [Id] [uniqueidentifier] NOT NULL, [Date] [datetime] NOT NULL, [Data] [nvarchar](255) NOT NULL, CONSTRAINT [PK_Table_DateTime] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Table_DateTime] ADD CONSTRAINT [DF_Table_DateTime_Id] DEFAULT (newid()) FOR [Id] GO
The above query creates table with an Id (UniqueIdentiefier), date (DateTime) and a data (Nvarchar) field. Next we need to insert some data into the table:
INSERT INTO [dbo].[Table_DateTime] ([Date],[Data]) VALUES ('05-24-2019 06:10:01.000','Item 1')
INSERT INTO [dbo].[Table_DateTime] ([Date],[Data]) VALUES ('05-24-2019 06:10:01.001','Item 2')
INSERT INTO [dbo].[Table_DateTime] ([Date],[Data]) VALUES ('05-24-2019 06:10:01.002','Item 3')
INSERT INTO [dbo].[Table_DateTime] ([Date],[Data]) VALUES ('05-24-2019 06:10:01.003','Item 4')
INSERT INTO [dbo].[Table_DateTime] ([Date],[Data]) VALUES ('05-24-2019 06:10:01.004','Item 5')
INSERT INTO [dbo].[Table_DateTime] ([Date],[Data]) VALUES ('05-24-2019 06:10:01.005','Item 6')
INSERT INTO [dbo].[Table_DateTime] ([Date],[Data]) VALUES ('05-24-2019 06:10:01.006','Item 7')
INSERT INTO [dbo].[Table_DateTime] ([Date],[Data]) VALUES ('05-24-2019 06:10:01.007','Item 8')
INSERT INTO [dbo].[Table_DateTime] ([Date],[Data]) VALUES ('05-24-2019 06:10:01.008','Item 9')
INSERT INTO [dbo].[Table_DateTime] ([Date],[Data]) VALUES ('05-24-2019 06:10:01.009','Item 10')
INSERT INTO [dbo].[Table_DateTime] ([Date],[Data]) VALUES ('05-24-2019 06:10:01.010','Item 11')
SELECT * FROM [dbo].[Table_DateTime] ORDER BY Date ASC
This gives the result shown below:
But wait, that’s strange, look for example of the date value in row 2, 4 and 5. That isn’t what I specified into the Date column in the insert statement. So then we started looking at the MSDN page for date. First thing the documentation states:
Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.
So this behaviour is a precision problem instead of a programming bug and after looking at the accury column in the table we find:
Accuracy
Rounded to increments of .000, .003, or .007 seconds
That looks exactly like the behavior we are seeing in our test table. So according to the docs datetime2 has a greater precision, so lets change our data type and run the select query again:
ALTER TABLE [dbo].[Table_DateTime] ALTER COLUMN [Date] DateTime2
This query at least yields the results I expected. After looking at the precision field in the documentation we find out the following:
Precision, scale
0 to 7 digits, with an accuracy of 100ns. The default precision is 7 digits.
So after investigating this issue this post could have better been called: “Why should you use datetime2 in SQL server”. But at least now I and hopefully you know.