Hi

I have a data for year 2013, 2014 and billions of records are there. I want to delete data for 2014. If I do truncate it is going to be faster but it will delete entire data for 2013 as well which I don't want however I just wanted to know is there any alternate solution for making delete faster.

It imho depends on the row per year distriution. As you can use TRUNCATE, you have no foreign keys pointing to your table. So you can rename it. Then recreate the table under its original name. Now you can copy the rows you want to keep.

On the other hand you can simlpy batch delete the rows you want to drop:

WHILE(SELECT COUNT(*) FROM yourTable WHERE Condition) > 0
BEGIN
    DELETE TOP(1000)
    FROM yourTable
    WHERE Condition;
END;

• Edited by Stefan HoffmannMVP 18 hours 36 minutes ago
• Proposed as answer by Praveen Rayan D'sa 18 hours 0 minutes ago

The fast way to delete a lot of data, but not all is to have a partitioned table or a partitioned view. But if you did not set up for that in advance, it is not going to help you know.

Deleting in batch as Stefan suggested is a good, provided that there is an index to support the condition for the deletion.

Yet an alternative is to create a new table and copy over the data you want to keep. This is a more advanced alternative, as it requires you to keep track of indexes, triggers, foreign keys and whatnots that also need to be copied.

If you are were using table partitioning, this delete would have been a metadata operation that would have taken about a minute or so to delete a year's worth of data.

Since you aren't using table partitioning, you can try to add a new compressed table (with similar schema as old table) in a new filegroup and move the data you want to retain and specify the table being moved to be compressed table, basically leaving the old table in the old filegroup with the data you want to delete and delete this old table and rename the new table with the old table name (note that data compression requires Enterprise Edition). 

usually we make use of partitioning for easier deletion of data from large tables. That would be fastest and easiest method.

Wherever that is not possible we can create an index on appropriate field(s) and put a logic to do deletion within loop in small chunks.

If you are were using table partitioning, this delete would have been a metadata operation that would have taken about a minute or so to delete a year's worth of data.

Since you aren't using table partitioning, you can try to add a new compressed table (with similar schema as old table) in a new filegroup and move the data you want to retain and specify the table being moved to be compressed table, basically leaving the old table in the old filegroup with the data you want to delete and delete this old table and rename the new table with the old table name (note that data compression requires Enterprise Edition).