Is filtering on int better than filtering on date [closed]

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.

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://docs.microsoft.com/en-us/sql/t-sql/data-types/data-type-precedence-transact-sql?view=sql-server-2017