Tell It To The Judge
A common dilemma is when you have two date columns, and you need to judge the gap between them for something.
For instance, say you have a table of orders and you want to figure out how long on average it takes an ordered item to ship, or a shipped item to be delivered.
You’re not left with many good ways to write the query to take advantage of indexes.
Let’s have a look-see.
We’re gonna skip the “aw shucks, this stinks without an index” part.
CREATE INDEX gloom ON dbo.Posts(CreationDate, LastActivityDate); SELECT COUNT(*) AS records FROM dbo.Posts AS p WHERE DATEDIFF(YEAR, p.CreationDate, p.LastActivityDate) > 9;
Because it still stinks with an index. Check it out.
Though we have a predicate, and an index on both columns, we don’t have anything to seek to.
Why? Because our predicate isn’t on anything that the index is keeping track of.
Indexes don’t care how many years, months, days, hours, minutes, seconds, milliseconds, or microseconds difference there are between these two columns.
That’d be a really cool kind of index to have for sure, but insert a shrug that fills your screen the way dark matter fills the universe here.
All we got is workarounds.
Another For Instance
We can use a computed column:
ALTER TABLE dbo.Posts ADD despair AS DATEDIFF(YEAR, CreationDate, LastActivityDate); CREATE INDEX sadness ON dbo.Posts(despair);
The result is something we can seek to.
Which is probably the type of plan that you’d prefer.
Thanks for reading!
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount on to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
- Using Views To Reduce Memory Grants In SQL Server
- Getting The Top Value Per Group With Multiple Conditions In SQL Server: Row Number vs. Cross Apply With MAX
- Getting The Top Value Per Group In SQL Server: Row Number vs. Cross Apply Performance
- Signs You Need Batch Mode To Make Your SQL Server Queries Faster