0

I have a system where most of our table has the current datetime as primary key and we filter most of the data based on the datetime.

The question, if I move this datetime to another new table and have an auto id in the new table. for eg.

Dates
id datetime
1  11/6/2019
2  11/7/2019
3  11/4/2019
4  11/8/2019

Now all the other tables will have id instead of datetime and filtering will be based on the id.

Will this improve performance or filter on a datetime is equal to filter on int?

I am using SQL Server 2017.

Improve this question
3
  • 1
    Date is 3 bytes where as INT is 4 bytes. There is one case proving the date to be faster in 2008 but only slightly. Before you over optimize, how would you know which ID's to filter on? Date's seem more logical for a human to enter as a predicate than a correlating ID that I presume you'd have to look up first anyway. This could be something for you to test and post the results!
    – S3S
    Commented Nov 27, 2019 at 16:28
  • @scsimon - it is datetime not date, sorry I just edited the question.
    – ABC
    Commented Nov 27, 2019 at 16:47
  • 1
    I agree with @scsimon. We need to see the queries, in the end. I doubt that you will just replace an WHERE OrderDate > '20180324' with WHERE OrderID > 34729. I.e., there will probably be changes to the queries and those are more likely to affect performance.
    – Tibor Karaszi
    Commented Nov 27, 2019 at 17:02

1 Answer 1

Reset to default
1

I would answer with: Keep it as it is with datetime!

One of the reasons is simple really, data type precedence. So, if you were ever to compare a datetime to an integer, the engine will decide that rather than converting date->int then compare int = int, it will convert int->datetime and compare datetime = datetime. Which stands the same for every single data type (like varchar) except for:

  1. user-defined data types
  2. sql_variant
  3. xml

in that order of precedence.

Date comparison functions are also optimized to a crazy degree and they run blitzing fast, so you won't be gaining much in terms of performance, in fact you might end up losing performance since you have to do a join operation and then (again) do a where(how else would you filter by dates after all, generated ID != date you're comparing with).

So you need to ask yourself, is it really necessary to move date into a separate table? If you're planning on having a Data Warehouse, it might help with making the TimeDim table but other than that I see no reason to do so.

If you are interested in data precedence, you can see the list here (highest to lowest, top to bottom): https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017

Improve this answer

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