Deleting old records in large table in SQL Server

Let's face it DELETE command is really slow. It is often quite a challenge to delete multiple records. If there is no WHERE clause then it is much better to use TRUNCATE, but if you have to use WHERE you are back to square one :). This is my approach how to use DELETE command on a large set of data:


DECLARE @rows INT = 1
SET @rows = 1

WHILE @rows > 0
BEGIN
DELETE TOP (5000) [dbo].[MyLog]
WHERE CreationDateTime <= DATEADD(MONTH, - 6, GETDATE())

SET @rows = @@ROWCOUNT;
END