Sql-server – Is it more efficient to subtract a value from a date or use DATEADD with a negative value

sql serversql-server-2008-r2

I have a stored procedure that I need to make more efficient. It currently looks like

delete_more:
    DELETE TOP(500)
    FROM [dbo].[Foo]
    WHERE [DateCreated] < DATEADD(DAY, -1 * @MaxAgeInDays, GETDATE())

    IF @@ROWCOUNT > 0
        GOTO delete_more

The first step was to extract some variables

DECLARE @Now DATETIME = GETDATE()
DECLARE @EarliestDate DATETIME = DATEADD(DAY, -1 * @MaxAgeInDays, @Now)

delete_more:
    DELETE TOP(500)
    FROM [dbo].[Foo]
    WHERE [DateCreated] < @EarliestDate

    IF @@ROWCOUNT > 0
        GOTO delete_more

But then I thought that perhaps it might be faster if I change @EarliestDate to

DECLARE @EarliestDate DATETIME = @Now - @MaxAgeInDays

Which one would be faster or more clear to both the database, and a dba?

Best Answer

The difference, if any, in performance should be so small that it wouldn't be measurable. The calculation is done either way just once. Deleting thousands of rows will surely need far more time to execute than a single calculation.

Having said that, I'd choose the version with the DATEADD() as adding a date with an integer doesn't look nice or elegant. OK, it may look a bit like a cool "hack" but it's not best practice and is relying on the internal representation of date/datetime types.

See Aaron Bertrand's blog: Bad Habits to Kick : Using shorthand with date/time operations, where he explains how this kind of code works ok with DATE or DATETIME but will break your queries when someone changes the type of the column/variable to DATETIME2.

Related Question