Partition table

Question

Partition table

hwi

SSC Rookie

Points: 40
More actions
June 4, 2003 at 1:05 am

#80999

I plan to use partition table since my data is too large. Could anyone help me give some explanation about how to create and manage it?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jpipes SSChampion Points: 12230 More actions · Answer 1

Option 1:

You can vertically partition a table by splitting the structure of a table into multiple tables based on column. For instance, you can have a main table with all frequently accessed columns and a supplemental table with less frequently accessed columns, joined on a unique key (one to one). This produces more efficient queries since, usually, there will be more extents read into memory for each request since the width of each data row is smaller.

Option 2:

You can horizontally partition your tables by splitting them by some range of values, typically by date range. For instance, Sales_1QTR2003, Sales_2QTR2003. You will have to maintain these tables through some sort of archival process, and create views which translate and aggregate these tables for your stored procedures. For instance, you can create a view called vSales_All which UNIONs all the separate sales tables, a view called vSales_2003 which UNIONs all the 2003 Sales tables, and so on. I believe Enterprise Editions allows more advanced indexing on these partitioned views. Perhaps someone with more experience in the arena may elaborate. But for now, you can look in BOL for more details.

Cheers,

Jay

David.Poole SSC Guru Points: 76001 More actions · Answer 2

I had a transactional database that held transactions for 6 european countries.

I split the transaction table into 12 separate tables. For each country I had

An "Active" table (because I figured out that 90% of queries were concerned with data in the past 2 quarters).
An "Archive" table for data older than that

My understanding with the users of the database was that they would accept slower queries on archived data.

Prior to leaving I was looking at splitting the archive tables into separate tables by year.

LinkedIn Profile

mvanchiere SSC Enthusiast Points: 188 More actions · Answer 3

I'd like to add something to the note from jpipes about horizontal partitioning.

We recently partitioned a 65 million row table into quarterly tables of 10 million or so records each. I followed the instructions in the topic "Creating a Partitioned View" in SQL Server Books Online. These instructions were very useful, and our partitioned view works just fine. The only catch was that some types of changes to the structure of the partition tables required check constraints to be "reapplied" in order for SQL Server to recognize the view as a real partitioned view and optimize properly. Hopefully, my comment will make sense after you look at "Creating a Partitioned View".

Our new partitioned view is working great. We've realized fairly significant performance improvements for all types of queries. We're using SQL Server Standard Edition, which works fine for reading from a partitioned view. Inserting records into partition tables "through" the partitioned view requires Enterprise Edition. We get around this by writing to the partition tables directly.

hwi SSC Rookie Points: 40 More actions · Answer 4

Is there any other solution to create partition table? I mean, in Oracle we could use the SQL CREATE TABLE with the PARTITION option. So we don't have to take care about the mechanism, because the system has handled it for us. Does SQL Server support like this? Thank you.