I am designing a table for a system that saves a number of logs. We are looking at ~200 entries per second.
We are using SQL-Server 2012 Enterprise Edition.
I have a question regarding breaking up a
Datetime column into two columns,
My thinking behind the question. I would say once the data is stored in the database, most searches are going to be based on a daily basis, give me all results from today / 10th of Jan.
Now I do still need to store the time as well. So if I store it as a
datetime, when executing this query sql will have to load the whole
datetime fields and then only look at half of the data.
So by possibly storing date in its own field it can look at only what it needs.
But then on the other hand if you do specify a time in your query, it now has to check the value of two columns.
So I would like the input from you SQL Gurus on which option would have better performance for queries on a large database.
For all I know
datetime could be highly optimised and a much better solution than breaking it up.