Vijay,

This is a gem. Nice work. I dont usually feel compelled to comment on forums but this saved me a lot of work and gave me an elegant solution, so well done.

A couple of comments.

I found that when dealing with a partition that had been row compressed the script did not create a row compressed empty table. This was because my partitions did not have an index = 1. Removal of this clause and everything ran smoothly.

Secondly, the name of this article is slightly misleading (apologies for the literalness). The script does not truncate a partition, it removes it, as the switched out partition is never switched back in. To truncate a partition, you need to switch out, truncate then switch back in.

For me the most beneficial aspects of this script is the ability to create an empty replica of an existing table through T-SQL.

Well done.

Paul.