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
int then compare
int = int, it will convert
datetime and compare
datetime = datetime. Which stands the same for every single data type (like
varchar) except for:
- user-defined data types
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