Today, I got following questions:

"The documentation states that Datetime2 is unaware of the time zone.
     Does that still guarantee that when the clock gets adjusted for Daylight Saving Time this will not result in duplicate timestamps? I.e. is Datetime2 internally mapped to UTC?
"

Here is the answer:

  • Datetime2 does not guarantee that you will get a unique value you would have to have a unique constraint on the column. If you have several batches running at the exact same time on a multiprocessor system you may end up with duplicate values without a constraint on the column, if you need guaranteed unique values you need to work with uniqueidentifier and the NEWID function. 
  • DateTime2 in SQL Server does not contain any information about
    the timezone of the value and whether the value is UTC or local time.  The SYSDATETIME, SYSUTCDATETIME,  SYSDATETIMEOFFSET built-in functions will adjust automatically when the Windows Operating System, which the SQL Server instance running on, adjusts the clock during daylight saving switch.  I.e.,  the datetime offset returned by SYSDATETIMEOFFSET will be changed from -08:0 to -07:00 for Pacific Time.  The SYSDATETIME value generated will have one hour gap when entering daylight saving, i.e., one hour is missing, but SYSUTCDATETIME does not.
  • My recommendation is that always store UTC time in datetime2 column or use SYSDATETIMEOFFSET data type, and handle local time issue by calling Windows/.Net API.