5

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, Date and Time.

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.

  • Why not a Date and a DateTime? – Serpiton May 27 '14 at 9:40
  • I dont see how that would be beneficial at all? Why repeat date? – Zapnologica May 27 '14 at 9:44
  • To use the DateTime field without manipulation when you need date and time, and to create an index on it. – Serpiton May 27 '14 at 9:50
  • The Date column would lend itself to columnstore compression because there are less distinct values. Might help PAGE compression as well. – usr May 27 '14 at 10:53
  • 1
    @Zapnologica did you ever come t a result on your question? I am interested. I would never expect that having two (partially redundant) information makes sense and the benefits are higher than the costs - but maybe you can tell us your findings? – Magier Aug 11 '16 at 8:20
4

I would use DATETIME2 as opposed to DATETIME for any new development work at this point. If you don't need sub-second granularity, use DATETIME2(0) and you'll see some space savings as well.

From here I would start experimenting. If performance is poor just querying off the single column, I'd then try adding persisted computed columns to separate out the date and time components. This will ensure that the separated DATE and TIME columns will always be the same as the "master" DATETIME2(n) column.

  • +1 on the datetime2 , I just need up milliseconds – Zapnologica May 28 '14 at 8:13
  • according to https://msdn.microsoft.com/en-us/library/bb677335.aspx the dateTime2(0) (1) ,(2) and (3) use the same amount of storage. – Zapnologica Jan 11 '17 at 11:32
  • DATETIME2(3) uses 7 bytes, you use 6 bytes if the precision is less than 3 (i.e. 0, 1, 2). – Frank Jan 12 '17 at 17:02
1

I would suggest two columns: DATE and DATETIME2(p), set the precision p as the minimum needed to support requirements and optimize storage. Then index them (possibly in combination with other columns) appropriately to support your queries.

If the majority of your queries are going to be using only the date, it makes sense to have a separate column for it, with appropriate index(es) to support the queries. One option is to make the date column a PERSISTED computed column (computed from the datetime2 column). For details about indexing a computed column, see this BOL link.

  • 3
    Not sure I understand what the additional date only column buys you. I can always get the data for a particular day using datetime_column >= 'yyyymmd1' AND datetime_column < 'yyyymmd2'. Why pay the storage and index maintenance costs for an extra column with redundant information? – Aaron Bertrand May 27 '14 at 13:37
  • Well, my assumption was that a query using just a DATE column (instead of DATETIME2) would be more optimal. Smaller data type, hence less pages to read? This + Zapnologica asking "...which option would have better performance for queries on a large database" was the basis for my suggestion. It does bring with it the overhead you mentioned (storage, index maintainance), I agree... I just assumed (maybe to fast :)) that was acceptable, as long as the query was optimal. Now, if my above assumption is wrong, I stand corrected (in that case, please explain where I made the mistake - thanks.). – Blaž Dakskobler May 27 '14 at 20:42
  • 1
    If the column is the only one in the index, and you're scanning just that index, perhaps. Usually, though, this is from a where clause that is filtering the rows and identifying which ones to include. This will usually end up with a seek (well, disguised as a range scan) unless the range is a decent % of the table. Even on a very large table, the difference in performing such an operation against an index consisting of a datetime or smalldatetime column, compared to a date column, is unlikely to make much of a difference in the overall query at all. And considering the cost... – Aaron Bertrand May 27 '14 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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