12 fraction digits in SQL Server

Question

We are currently migrating from DB2 to SQL Server. DB2 supports TIMESTAMP(6-12) while DATETIME2 only supports up to 7 decimals. Since we have many keys represented as TIMESTAMP(12) what would be the best way of migrating these to SQL Server?

I was thinking we should store them as varchar(32) or char(32). This will allow storing all 12 fraction digits.

I assume order by ascending/descending will work the same with varchar/char as a datetime2.

I assume all SQL in the application can remain the same as datetime2 / timestamp values are represented as strings in SQL.

We will however have issues using time functions in SQL Server unless we cast back and forth (which probably won't perform well).

Any better suggestion?

Answer 1

Storing date/time data in a character-based column is a seriously bad idea. And, no, datetime2/timestamp values are not represented as strings in SQL Server. See Aaron Bertrand's excellent bad-habits blog series on the topic.

DB2's timestamp data type¹ is:

a seven-part value representing a date and time by year, month, day, hour, minute, second, and microsecond, in the range of 0001-01-01-00.00.00.000000000 to 9999-12-31-24.00.00.000000000 with nanosecond precision. Timestamps can also hold timezone information

This equates nearly perfectly to a datetime2(7) data type in SQL Server².

Time portion: 0 to 7 digits, with an accuracy of 100ns. The default precision is 7 digits.

Unless you are measuring exceptionally accurate particle accelerator physics, you likely don't need precision beyond 1 ns.

What time source are you using?

You mention in your question you are using these high-precision timestamp values as keys. I would investigate the appropriateness of storing those values in a numeric(32,12) column in SQL Server.

This is a sample table with that type of key column:

IF OBJECT_ID(N'dbo.TimeTesT', N'U') IS NOT NULL
DROP TABLE dbo.TimeTest;
CREATE TABLE dbo.TimeTest
(
    ItemKey numeric(32,12) NOT NULL
        CONSTRAINT PK_TimeTest
        PRIMARY KEY CLUSTERED
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

Here, I insert a very large and precise value into the column:

INSERT INTO dbo.TimeTest (ItemKey)
VALUES (99999999999999999999.123456789012);

What that looks like:

SELECT *
FROM dbo.TimeTest;

+-----------------------------------+
|              ItemKey              |
+-----------------------------------+
| 99999999999999999999.123456789012 |
+-----------------------------------+

In order to successfully import your DB2 timestamp(12) values into SQL Server you'll need to export them as numeric values with the appropriate magnitude and precision. If you can do that, you won't lose any data, and will gain the ability for SQL Server to properly validate data used in the key columns. A numeric column is also extremely efficient for sorting, since it is not locale or collation sensitive.

Using a varchar(45) would allow keys to be created like this is a very long, non-numeric key; which would clearly be a bad idea.

Answer 2

Break up the timestamps into 2 columns: a datetime2(7) column that stores to the hundreds of nanoseconds and an int* column that captures the remainder.

+----------------------------------+-----------------------------+-------+
|         DB2 timestamp(12)        |   SQL Server datetime2(7)   |  int  |
+----------------------------------+-----------------------------+-------+
| 2017-04-08 14.43.52.456017331416 | 2017-04-08 14:43:52.4560173 | 31416 |
+----------------------------------+-----------------------------+-------+

Wherever you require a key with DB2-level precision, use a composite key with both columns.

When you don't need precision beyond 7 decimal places, you can leave the remainder column untouched. After all, adding microseconds to a timestamp will not change any digits beyond 6 decimal places. So, you can still use SQL Server's date/time functions on the datetime2 column. Beyond that, you're on your own.

Since you're migrating to SQL Server, I would hope that any new data or new business requirements would not exceed the precision that datetime2 can handle, and that you're only looking to accommodate data from your legacy system. As old data is archived, the need for the remainder column, and custom functions using it, should decrease over time.

*If you start building custom functions to replicate DATEADD/DATEDIFF functionality for increments/durations/TimeSpans smaller than 100 nanoseconds, add a constraint to keep the remainder column between 0 and 99999.