title: Proper way to index date & time columns
body: <p>I have a table with the following structure:</p>
  <pre><code>CREATE TABLE MyTable (
     ID int identity,
     Whatever varchar(100),
     MyTime time(2) NOT NULL,
     MyDate date NOT NULL,
     MyDateTime AS (DATEADD(DAY, DATEDIFF(DAY, '19000101', [MyDate]), 
                            CAST([MyDate] AS DATETIME2(2))))
  )
  </code></pre>
  <p>The computed column adds date and time into a single datetime2 field.</p>
  <p>Most queries against the table have one or more of the following clauses:</p>
  <pre><code>... WHERE MyDate &lt; @filter1 and MyDate &gt; @filter2
  ... ORDER BY MyDate, MyTime
  ... ORDER BY MyDateTime
  </code></pre>
  <p>In a nutshell, <code>date</code> is usually used for filtering, and full <code>datetime</code> is used for sorting.</p>
  <p>Now for questions:</p>
  <ol>
  <li><p>What is the best way to set indices on those 3 date-time columns? 2 separate on <code>date</code> and <code>time</code> or maybe 1 on <code>date</code> and 1 on composite <code>datetime</code>, or something else? Quite a lot of inserts and updates occur on this table, and I'd like to avoid over-indexing.</p>
  </li>
  <li><p>As I wrote this question, I noticed the long and kind of ugly computed column definition. I picked it up from somewhere a while ago and forgot to investigate if there's a simpler way of doing it. Is there any easier way of combining a <code>date</code> and <code>time2</code> into a <code>datetime2</code>? Simple addition does not work, and I'm not sure if I should avoid casting to <code>varchar</code>, combining and casting back.</p>
  </li>
  </ol>