StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>Note:</strong> When I originally wrote this answer I said that an index on one of the columns could create a query that performs better than other answers (and mentioned Dan Fuller's). However, I was not thinking 100% correctly. The fact is, without a computed column or indexed (materialized) view, a full table scan is going to be <em>required</em>, because the two date columns being compared are from the <em>same</em> table!</p> <p>I believe there is still value in the information below, namely 1) the possibility of improved performance in the right situation, as when the comparison is between columns from different tables, and 2) promoting the habit in SQL developers of following best practice and reshaping their thinking in the right direction.</p> <p><strong>Making Conditions Sargable</strong></p> <p>The best practice I'm referring to is one of moving one column to be alone on one side of the comparison operator, like so:</p> <pre><code>SELECT InitialSave = DateDiff(second, T.BegTime, T.EndTime) FROM dbo.MyTable T WHERE T.EndTime <= T.BegTime + '00:00:10' </code></pre> <p>As I said, this will not avoid a scan on a single table, however, in a situation like this it could make a huge difference:</p> <pre><code>SELECT InitialSave = DateDiff(second, T.BegTime, T.EndTime) FROM dbo.BeginTime B INNER JOIN dbo.EndTime E ON B.BeginTime <= E.EndTime AND B.BeginTime + '00:00:10' > E.EndTime </code></pre> <p><code>EndTime</code> is in both conditions now alone on one side of the comparison. Assuming that the <code>BeginTime</code> table has many fewer rows, and the <code>EndTime</code> table has an index on column <code>EndTime</code>, this will perform far, far better than anything using <code>DateDiff(second, B.BeginTime, E.EndTime)</code>. It is now <em>sargable</em>, which means there is a valid "search argument"--so as the engine <em>scans</em> the <code>BeginTime</code> table, it can <em>seek</em> into the <code>EndTime</code> table. Careful selection of which column is by itself on one side of the operator is required--it can be worth experimenting by putting <code>BeginTime</code> by itself by doing some algebra to switch to <code>AND B.BeginTime > E.EndTime - '00:00:10'</code></p> <p><strong>Precision of DateDiff</strong></p> <p>I should also point out that <code>DateDiff</code> does not return <em>elapsed</em> time, but instead counts the number of <em>boundaries</em> crossed. If a call to <code>DateDiff</code> using seconds returns <code>1</code>, this could mean <code>3 ms</code> elapsed time, or it could mean <code>1997 ms</code>! This is essentially a precision of +- 1 time units. For the better precision of +- 1/2 time unit, you would want the following query comparing <code>0</code> to <code>EndTime - BegTime</code>:</p> <pre><code>SELECT DateDiff(second, 0, EndTime - BegTime) AS InitialSave FROM MyTable WHERE EndTime <= BegTime + '00:00:10' </code></pre> <p>This now has a maximum rounding error of only one second total, not two (in effect, a floor() operation). Note that you can only subtract the <code>datetime</code> data type--to subtract a <code>date</code> or a <code>time</code> value you would have to convert to <code>datetime</code> or use other methods to get the better precision (a whole lot of <code>DateAdd</code>, <code>DateDiff</code> and possibly other junk, or perhaps using a higher precision time unit and dividing).</p> <p>This principle is especially important when counting larger units such as hours, days, or months. A <code>DateDiff</code> of <code>1 month</code> could be 62 days apart (think July 1, 2013 - Aug 31 2013)!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload