Solution to TSQL Table Truncation Challenge

Last week I provided a challenge that required you to come up with a solution for fast clearing of a table that has an indexed view defined on it, without dropping the indexed view. You can find the puzzle details here. Thanks to all those who sent solutions; there were a few interesting suggestions. The first who came up with the solution I was aiming at was Peter Larsson (Peso), as usual. :)

In SQL Server 2005 and on, even when you don’t explicitly partition your tables and indexes, they are internally always partitioned. It’s just that without explicit partitioning each table or index has only one partition. SQL Server 2008 enhances partitioning support by introducing a feature called partition-aligned indexed views, which means that the query processor automatically maintains an indexed view created on a partitioned table when a new partition is switched in. When you switch a partition in or out of a partitioned table, you are no longer required to drop the index on the view and re-create it later.

Back to our puzzle, the new feature allows very fast clearing of a table that has an indexed view on it by creating a staging table (call it T1_Stage) with the same structure as our T1 table, switching T1 to T1_Stage, and then dropping T1_Stage. All this can be done without dropping the view or even the index on it. Here’s the code to achieve the task:

CREATE TABLE dbo.T1_STAGE

(

  col1 INT NOT NULL PRIMARY KEY,

  col2 INT NOT NULL,

  col3 NUMERIC(12, 2) NOT NULL

);



ALTER TABLE dbo.T1 SWITCH TO dbo.T1_STAGE;



DROP TABLE dbo.T1_STAGE;

Cheers,

BG