1

I am developing an ASP.Net application using SQL Server. Looking up on forums I found that the best practice to deal with big databases is to use indexes and partitions. But I didn't find anywhere how to do this programmatically in application. I also wonder how to make a select in a specific partition, I mean that SQL shall avoid the other partition and look just in the partition I had specified. This shall be done also programmatically

porandddr
  • 129
  • 1
  • 1
  • 6
  • And just how would your application know which partition to look in? I don't think you are looking at partitions correctly. – paparazzo Jul 23 '15 at 12:34

1 Answers1

0

You can have multiple partition strategies:

  • Round-robin ('id%n==0')
  • Time-based
  • Custom (using a mapping table)

Furthermore, you will need to expand on this subject. Regarding the above-mentioned strategies I'll try to quickly and briefly mention some details.

  1. Round-robin : Considering that you have n partitions any new item created having and id of m should go in partition with id m%n. For example having 4 partitions (identified in a 0-based index) called p0, p1, p2 and p3, and having had created item with id 573 this should go in partition p1 because the remainder of dividing 573 to 4 (4 being the number of partitions) will yield 1.

  2. Time based : Used sometimes in statistics data, data-warehousing, big-data etc. You will have partitions for several segments of time, for example a table for each month, or day, or hour. Newly created items will go in their corresponding partition based on the creation time.

  3. Custom : You will define your own partition strategy and use a mapping table to map the item id and the partition id in which it resides. This is the most flexible but might not be easiest to implement or the most performant.

Andrei Rînea
  • 20,288
  • 17
  • 117
  • 166
  • I decided to use Time based strategy. Let's consider I have 3 partitions named: "2013", "2014", "2015". Now, how could I look up for data just in partition "2013"? Or the SQL knows by default in which partition to look? – porandddr Jul 23 '15 at 13:12
  • The partition table will be queried just like any other table. – Andrei Rînea Jul 23 '15 at 13:19