Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jtwine
Starting Member
2 Posts |
 Posted - 2007-10-23 : 14:14:03
|
| How do I create a stored proc that will find those records with a column "created_date" older than 14 days and then delete those records? |
|
|
cognos79
Posting Yak Master
241 Posts |
Posted - 2007-10-23 : 14:22:14
|
| delete from table_namewhere datediff(day,getdate(),created_date) < 14 |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-23 : 14:24:59
|
You might be better off withDELETE DFROM MyTable AS DWHERE created_date < DATEADD(Day, -14, getdate()) because it will be more likely to use any index on [created_date]Kristen |
|
|
|
jtwine
Starting Member
2 Posts |
Posted - 2007-10-23 : 14:30:38
|
| Interesting, for a sql newbie....thanks for the help. |
|
|
|
cognos79
Posting Yak Master
241 Posts |
Posted - 2007-10-23 : 14:38:48
|
| kristen...out of curiosity "because it will be more likely to use any index on [created_date]"whats the difference between the statement i suggested and yours. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-23 : 15:34:14
|
"whats the difference between the statement i suggested and yours."Your use of the function wrapper "datediff()" on the column [created_date]:where datediff(day,getdate(),created_date) < 14 will generally prevent the optimiser using any (available) index on the column [created_date]Kristen |
|
|
|
cognos79
Posting Yak Master
241 Posts |
Posted - 2007-10-23 : 16:17:27
|
| Thanks for clarification. How do i know tht optimiser prevents using indexes if i use some function like datediff. Can I read some articles tht sheds some light abt this. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-24 : 04:35:04
|
| You can check the Query Plan to see which indexes are (or are NOT!) being usedKristen |
|
|
|
|
|
|
Stored proc to delete aged records