Why do we have to use SWITCH in table partitioning?

Question

I'm a beginner in SQL.

I'd like to know what is the importance of SWITCH in table partitioning?

I've created one table partitioned month-wise, with a total of 12 partitions.

The ranges are:

'2012-01-31 23:59:59.000', 
'2012-02-29 23:59:59.000', 
'2012-03-31 23:59:59.000', 
'2012-04-30 23:59:59.000', 
'2012-05-31 23:59:59.000', 
'2012-06-30 23:59:59.000', 
'2012-07-31 23:59:59.000', 
'2012-08-31 23:59:59.000', 
'2012-09-30 23:59:59.000', 
'2012-10-31 23:59:59.000', 
'2012-11-30 23:59:59.000', 
'2012-12-31 23:59:59.000'

Answer 1

Switch is an extremely efficient way to add or remove data to your table.

You can switch in data, which is the preferred way to upload data in an ETL process. By preparing the data in a staging table (completely unrelated to your actual data) you can prepare it w/o concurrency issues (your ETL won't block reporting) and present the new data in one single switch, thus becoming visible all at once.

You can switch out data, which is the preferred way of removing data past the mandated retention period. This allows to TRUNCATE the data after switch out, efficiently and minimally logged. In many environments this is the only way to delete data due to high concurrency.

Basically any operation for which enabling partitioning makes sense requires SWITCH. If you have already enabled partitioning and still look for an explanation for SWITCH then is is almost certain you did not need partitioning to begin with.