2

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?

  • 1
    Do you really need to store all the precision? Can't you truncate the values to 6 or 7 digits? – ypercubeᵀᴹ Apr 5 '17 at 19:48
  • Who actually creates Timestamps with 12 fractional digits, a picosecond resolution? Most DBMSes will not support that in a native datatype. – dnoeth Apr 5 '17 at 19:54
  • Yes, I do need this resolution. There is a reason DB2 can support up to 12 digits and Oracle can support up to 9. Certain platforms (besides x86) do support this resolution in the clock and work fast enough to need this resolution. And because we are migrating a existing database using this precision we have to find a equivalent data type in MSSQL. – Kelvin Wayne Apr 6 '17 at 7:34
  • 1
    So, they're keys? Is the actual date/time value itself import to retain as a date/time ? – Max Vernon Apr 6 '17 at 17:31
3

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.

  • Yes, I do need this resolution. There is a reason DB2 can support up to 12 digits and Oracle can support up to 9. Certain platforms (besides x86) do support this resolution in the clock and work fast enough to need this resolution. And because we are migrating a existing database using this precision we have to find a equivalent data type in MSSQL. So the answer is not "don't do it, it is a bad idea", but rather, which solution is the least troublesome ? – Kelvin Wayne Apr 6 '17 at 7:34
  • I've updated my answer to show a possible method of storing the resolution you require without resorting to using a character-based column. – Max Vernon Apr 6 '17 at 18:37
  • Wouldn't a simpler solution be to use varchar with a trigger to validate the value, if the concern is that a non-timestamp value is stored ? – Kelvin Wayne Apr 7 '17 at 9:41
  • @KelvinWayne - you could use a trigger or a check constraint, but storing date data in a character column is just asking for a multitude of potential problems. Storing numeric data in a numeric datatype is the best way to do it. What format do you plan on using for the datestamp if you do store in a char-based column? – Max Vernon Apr 7 '17 at 15:46
  • A Datetime2 format. This would allow me to cast it to datetime2 if needed to perform timebased functions (although only on 7 fraction digits). With regards to using a numeric data type, I agree, however the challenge will be that all read and write to those columns will require a conversion. This increases the complexity of the applications and SQL clients. By using a varchar I assume we can retain the SQL statements as-is on INSERT, UPDATE, DELETE, SELECT comparison and SELECT ORDER BY. But before we start testing on this I wanted to check with stackexchange to see if there are better ways. – Kelvin Wayne Apr 8 '17 at 9:57
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.