Sql-server – How to truncate an SSAS Tabular partition

Yes, this is possible. Here is how

1. Create a copy of the table (you can use the partition management tool for that: https://sqlpartitionmgmt.codeplex.com/)
2. Switch the partition you want to "compress" into the copy
3. Rebuild the indexes on the copy with FF = 100.
4. Switch the partition back into the original table

It should be pretty trivial to automate this with a SQL agent job.

Step 3 is best done in this order:

• Drop all secondary indexes
• Rebuild the cluster index with FF = 100
• Create all the secondary indexes again with FF=100

Also, if you have SQL Server Enterprise Edition, you could compress the data with PAGE compression. This would require a different process:

1. Create a copy of that table as above
2. Switch the data you want to compress into the copy
3. Compress the data by rebuilding indexes as above
4. ALTER the original table to enable page compression on the now empty partitions (the ones you switched out)
5. SWITCH the compressed data back into the original table.

With SQL Server 2014 Enteprise Edition, you can do the above as an online operation directly on the partition you want to compress. However, it is still a good idea to drop the secondary indexes in a copy first, because it will typically give you better data layout on the disk.

I thought RANGE LEFT or RANGE RIGHT just controlled if the border value went with the left or right partition. But it clearly does something else too.

Is there more to RANGE LEFT and RANGE RIGHT that I don't understand?

Yes, there is other significance to RANGE LEFT and RIGHT as your scripts demonstrate. The range specification determines if the newly created partition is to the left or right of the existing split partition. The range specification also determines the partition removed by MERGE, which is the partition that includes the existing boundary.

In the first RANGE LEFT script, the existing 4th partition is split into 2 adjacent ones, with the new one on the left. The new partition becomes partition #4 and the existing one is renumbered to #5. Rows from the existing partition (now #5) must be moved to #4 to accommodate the new boundaries, hence the excessive logging. Movement during partition SWITCH (or MERGE) requires about 4x the logging of normal DML operations so it is important to plan as to avoid data movement, especially with large tables that are often used in table partitioning scenarios.

No data movement is needed in the RANGE RIGHT script because the new partition (#5) is created to the right of the existing partition 4 and the existing partition 4 already conforms to those boundaries (data >= 3000 and < 4000).

Personally, I think RANGE RIGHT is more intuitive, especially with incremental partitioning column values. See http://www.dbdelta.com/category/table-partitioning/ for more gotchas.