Improve performance by partitioning I'm using SQL Server 2012. I’m using CTE to create an orders table from the items table. The final count for the orders table is around 120 million. It is taking about 2 hours for the whole process.

Can partitioning the table help reduce the completion time?

If so, can I partition an empty table by order year and then insert into the orders table from the CTE. Or will I need to create a partition on the items table since the CTE is rolled up from the items table? 

Also my estimated number of rows differ significantly from the actual number of rows.
Can partitioning the table help reduce the completion time?


No. Partitioning is not a performance feature, is used for other purposes. If the table has 120 million rows unpartitioned, it will also have 120 million rows after partitioning. Read How To Decide if You Should Use Table Partitioning.

If you want to improve performance you need to identify the bottleneck and address them appropriately. Is it a bad plan? Missing index? Slow IO? Slow CPU? Not enough RAM? Poor code? USe a methodology like Waits and Queues to identify the bottleneck.