CODEWITHSUNDEEP
sql-server-2005partitioning
Vishal
Vishal

Reputation: 12369

Questions with partitioning in sql server 2005?

I have been reading quite a few articles on partitioning tables. I have the following questions currently related to partitioning in sql server 2005 -

  1. I am basically want to do it so that inserts will be quicker on that table is that true?
  2. Will my queries on C# side or anywhere wrt to that table change all over the place if I partition say a table into 4 partitions ? Or is it transparent ?
  3. Are there any other ways to speed up the inserts on the table ?
  4. Is there a real simple article where I can just know how I can partition an existing table into x partitions based on the id values? I found this article.
  5. Is there anyway to do it other than enterprise edition of sql server?

Upvotes: 0

Views: 175

Answers (1)

CodeMonkey1313
CodeMonkey1313

Reputation: 16031

1 - This is a tricky question as it depends. Partitioning must be done on an indexed column, so it might be a bit quicker since the index wouldn't need rebuilt on the whole table. Partitioning is really more there to help speed up select queries, not insert queries. The select operation will be done seperately separately on each partition, then the results will be aggregated for you. Before SQL 2005 this was possible to done manually, but it wasn't nearly as optimized.

2 - The change is entirely transparent.

3 - Honestly the less indices you have the faster an insert will run.

4 - This is a great resource, and so is this. Basically if you already have data you have to recreate the table with the partitions then load the data from the old table to the new one and re-establish your relationships.

5 - You could manually do the partitioning with multiple tables with a view above those tables to join the tables together to get your data. This is known as a partitioned view and how partitioning was done in SQL 2000.

Upvotes: 2

Related Questions