12 fraction digits in SQL Server

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?

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


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.

StackExchange Database Administrators Q#169243, answer score: 3