CODEWITHSUNDEEP
sql-servert-sql
randmatt
randmatt

Reputation: 159

datetime vs bigint

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 -

Upvotes: 1

Views: 3457

Answers (2)

James K. Lowden
James K. Lowden

Reputation: 7837

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.

Upvotes: 4

Grzegorz Gierlik
Grzegorz Gierlik

Reputation: 11232

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.

Upvotes: 1

Related Questions