[mwlees]

I have a table, 'ct' which has around 4 million rows in it.

I have a clustered index on one of the date columns, 'ct_date', inside this table.

I have a stored procedure I use to delete 'ct' entries from a particular date or todays date if non supplied.

Here is the sp.

create proc del_ct
    @from_date    datetime = '1/1/1900'
as
begin
    declare  @error        int,
                @deleted    int

    if @from_date = '1/1/1900'
    begin
        select @from_date = convert( char( 10 ), getdate(), 101 )
    end

    begin transaction

    delete from ct where ct_date > @from_date

    select @error = @@error, @deleted = @@rowcount

    if @error = 0
    begin
        commit transaction
        print "%1! trades deleted", @deleted
    end
    else
    begin
        print "Error %1!", @error
        rollback transaction
    end

end
go

For some reason the clustered index is not chosen when I examine the query plan. This is borne out by the fact that this sp takes ages to run, i.e. it is table scanning 4 million rows....

I modified it to this

create proc del_ct
    @from_date    datetime = '1/1/1900'
as
begin
    declare  @error        int,
                @deleted    int,
                @actual_date datetime


    if @from_date = '1/1/1900'
    begin
        select @actual_date = convert( char( 10 ), getdate(), 101 )
    end
    else
    begin
        select @actual_date = @from_date
    end

    begin transaction

    delete from ct where created_date > @actual_date

    select @error = @@error, @deleted = @@rowcount

    if @error = 0
    begin
        commit transaction
        print "%1! trades deleted", @deleted
    end
    else
    begin
        print "Error %1!", @error
        rollback transaction
    end
end
go

And the clustered index is now being used...

Can anyone shed any light on this. It appears that the use of parameters, or the possible change of a parameter, precludes use of an index.