June 4, 2003 at 1:05 am
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?
June 4, 2003 at 5:52 am
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
June 5, 2003 at 2:05 am
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
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.
June 5, 2003 at 10:06 am
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.
June 11, 2003 at 7:42 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply