asked
Partitioning large tables
All, We are using sql server 2008 sql server standard and there are quite a few number of the tables that are growing large and i wnat to partition them so that it will improve performacne in retreiving the data. the tables are in the structure of 3 level joins where the data is stored. I want to do a file group partitioning, so that i put one year worth of the data in one single file group. To be more specific there are three schemas a.table1, a.table 2 and a.table3 b.table1, b.table2 and b.table3 like wise schema c also. The relationships are as follows: and a.table1.key = a.table2.key and a.table2.key2 = a.table2.key2 This is the same kind of relationships among tables in the three differnet schemas. i want to do a filegroup partiiting for slicing these tables among the schemas. i want to keep all one year worth data of all these 3 different schemas in one single file?? Is this an achivable task, else what is the best way to do it ? Thanks
In general partitioning is available only in Enterprise version of SQL Server. However on SQL Server Standard edition you can make a Partitioned Views. See [Using Partitioned Views][1] on MSDN. So if you would like to partition data in a single table which contains multiple years, then on Standard edition you will have to create multiple tables (one for each year and each table on different file group) which will be union-ed by the Partitioned View. If you would like to use real partitioning (using partition functions and partition schemes) you will have to switch to Enterprise version. [1]:
http://msdn.microsoft.com/en-us/library/ms190019(v=sql.100).aspx
Just to further clarify, since you want each table of each schema for same year should reside in the same file-group, so I beleive @Pavel Pawlowski was asking for different file-groups for per schema tables. Moreover, few things could give you optimal performance. Like, at-least the recent year file group resides on a different hard drive and history tables residing on different hard drive. Based upon your environment, you could make history file-group read-only as well.
Something to keep in mind, The only reason I say this is it just came around and bit me... If you have an environment such as reporting or QA where you might have a version of SQL Server installed that is not the same as the Enterprise Edition then you will not be able to restore your database to there. So where I was, the budget allowed for EE in production, but our reporting system was still standard. Therefore if we were to run with partitioning in production we were going to have to find a new way to get the data to the reporting server outside backup/restore. Not sure if this even applies to you, just figured I would share.
One thing that we have considered is using replication in place of the Backup\Restore operations. The backup\restore is not really the best way to do what they want, however there were some things that they had not considered, and now it is going to take a while to get off the code that they put around it.
