SQL Server – Why Use ‘Truncate Table’ Instead of ‘Truncate’

Is there no way to call the stored procedure and pass it a table?

There is, if you're using SQL Server 2008 or higher: table-valued parameters (TVPs).

I've already blogged about how to use TVPs in SSIS. Essentially, you have to use ADO.NET inside a Script Component. While this isn't the prettiest solution, it gets the job done relatively cleanly with all the code in one spot.

If I keep data for 6 days per month, instead of 1 day per month, will my queries perform slower?

It depends.

No - if you run exactly the same queries as before (no access to the new data at all).

SQL Server's partitioning implementation creates a separate rowset for each partition, so when you create a partitioned index, it creates a separate b-tree structure* for each partition (note that partitioned heaps also exist).

Simply adding more partitions therefore changes nothing from the point of view of the existing partitions - the indexes are exactly the same. Your queries access only a single partition, so nothing changes.

Maybe - if you query the new data at any stage. Bringing the new data into memory may displace data needed by the original queries, depending on the amount of memory you have. If the change results in new physical I/O, you will see an impact on performance, with the severity depending on the capability of the storage subsystem.

* This is mentioned in many places in the product documentation, for example:

• Clustered Index Structures
• Nonclustered Index Structures
• Heap Structures

From those links:

When a clustered index has multiple partitions, each partition has a B-tree structure that contains the data for that specific partition.
When a nonclustered index has multiple partitions, each partition has a B-tree structure that contains the index rows for that specific partition.
When a heap has multiple partitions, each partition has a heap structure that contains the data for that specific partition.

You can also explore this for yourself by looking at the system catalog views like sys.partitions, which shows the hobt_id (heap or b-tree id) of the structure that contains the rows for a particular partition.