Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'
share|improve this question
4  
In the partition function, I strongly recommend using lower boundaries instead of upper boundaries for date ranges. The ranges you gave are a very good example of why -- a partition key input of '2012-01-31 23:59:59:500' will unexpectedly land in the wrong partition. Using lower boundaries will also avoid painful coding when creating new boundaries in a sliding window scenario. –  Jon Seigel Sep 4 '12 at 1:57
    
actually i preferred range as '2012-01-31 23:59:59:997'. –  cgsabari Sep 5 '12 at 11:00
    
That still allows invalid values if you use datetime2. –  Jon Seigel Sep 5 '12 at 15:15
add comment

1 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.