datetime vs bigint

Question

I have several tables that must be joined based on start and stop dates, for instance ...

SELECT * FROM [t1] JOIN [t2] ON [t2].[start] BETWEEN [t1].[start] AND [t1].[stop] OR [t2].[stop] BETWEEN [t1].[start] AND [t1].[stop] OR [t2].[start] < [t1].[start] and [t2].[stop] > [t1].[stop]

These tables could be in the multiple-millions of rows.

I have the option to store the start/stop as datetime2 and/or bigint(nanoseconds since epoch). Given this option - would there be a performance gain by using the bigint if indexed?

Any pros/cons with either approach?

I would expect the integer approach to be faster -

Do you really need till nanosecond resolution or is seconds enough for you? — erichste
– erichste, Commented Jan 9, 2013 at 8:37
Unless the query runs in nanoseconds you will miss the nanosecond so I agree with erich007 as well do you need this resolution? — Namphibian
– Namphibian, Commented Jan 9, 2013 at 8:38
Not answering your question but a comment on the condition. It is equivalent to the simpler: ON [t2].[start] <= [t1].[stop] AND [t2].[start] <= [t1].[stop] — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jan 9, 2013 at 8:44
Here is a nice discussion maybe it can help you: dba.stackexchange.com/questions/25761/… — erichste
– erichste, Commented Jan 9, 2013 at 8:47
Correction, I meant: [t2].[start] <= [t1].[stop] AND [t1].[start] <= [t2].[stop] — ypercubeᵀᴹ
– ypercubeᵀᴹ, Commented Jan 9, 2013 at 8:52

James K. Lowden · Accepted Answer · 2013-01-10 05:33:51Z

Use datetime2. In addition to "easier to read by human", easier to program by a human. If you don't use a date type to represent a date, you'll have problems computing intervals, joining to other tables, comparing to external times e.g. getdate().

As far as efficiency goes, for purposes of comparison the server almost certainly treats all date types as integers. The only speed advantage that might be open to you, depending on whether you usually care about the seconds, would be clustering on smalldatetime, because it's 4 bytes. You could still keep the high-resolution part as a separate real.

Grzegorz Gierlik · Accepted Answer · 2013-01-09 08:50:15Z

As long as you use datetime2 type probably there would be no difference, because both types are similar at size (datetime2 -- 6 to 8 bytes, bigint -- 8 bytes).

I assume IO cost will be the only bottle neck, so it depends on how many records you compare (records to read) and how indexes will store datetime2 type.

I would test & compare both options in your case and choose the better one keeping in mind that datetime is easier to read by human.

Collectives™ on Stack Overflow

datetime vs bigint

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related