• Forget partitioning for now. With no experience in it you will be just as likely to mess something up as not since it is a VERY complex subsystem.

    For the deleting, there are several questions:

    1) how much data do you need to delete compared to total data in table?

    2) does the table need to stay online during this evolution?

    The best way to delete the data is with batched index seeks. With such a large amount of index space on the table hopefully you have an index you can seek to grab 50-100K rows per DELETE using an index seek. Combine this with transaction control and error handling and you can delete records efficiently without locking anything more than pages during the delete. There are other things that we will discuss depending on your answer to the 2 questions above.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service