6

Prior to executing a sensitive sql command I wanted to do a sanity check.

I am trying to delete records that have a [LoadDt] date value older than 3 days and my code is:

delete IntraDayStats
where DATEDIFF(dd, LoadDt, dateadd(d,-3, getdate()) ) >= 3

I want to schedule this as a sql job so that my IntraDayStats table has a rolling 3 day history. The job will run nightly.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
kacalapy
  • 9,806
  • 20
  • 74
  • 119

1 Answers1

9
where DATEDIFF(dd, LoadDt, dateadd(d,-3, getdate()) ) >= 3

is not sargable (an index won't be used), use

where LoadDt < getdate()- 3

Next time if you want to check, make the DELETE a SELECT and see what you get back

SQLMenace
  • 132,095
  • 25
  • 206
  • 225
  • whats is sargable ? i never heard of this... i did the select but wanted to double check the logic for a rolling three days. thanks – kacalapy Jan 03 '11 at 15:05