In this post, we will show and explain a small TSQL Sql Server 2008 procedure that deletes all rows in a table that are older than some specified date. That is, say the table has 10,000,000 rows in it the accumulated over the past 2 years.
Say you want to delete all but the last 30 days of activity. If you just simply say DELETE FROM table WHERE id>10000, you will cause this to happen in one transaction and likely, you will get an error. That’s the best case. The worst case is your system tries to do this, eventually consumes all the resources in your computer and crashes your server.
(more…)
In this post, we’ll go briefly the process of how you would update all rows in a SQL Server 2008 table such that a particular date column will be moved back 1 hour in time. This is actually pretty simple, but being that I typically do my work in the ORM layer (that is LINQ2SQL or Entity Framework), I just don’t do much of this. The process I’m going to follow is first to use Microsoft SQL Server Management Studio to make sure I know what I’m doing with the Sql Server 2008 functions, then add it to an UPDATE statement.
(more…)
I often speak very highly of LINQ and also LINQPad. This morning, I was struggling with some sql that would let me do a count by DateTime while stripping out the time portion. That is, I just want to know how many entries are in the table for each Date (regardless of what time). I tried lots of solutions I got from search, and they all gave not correct results, usually involving Casting and other non fun sql programming constructs.
(more…)
