Database Reference
In-Depth Information
How to do it...
The following process is essential to implement the horizontal process that will enable you to
design a data partitioning solution for tables and indexes.
Initially, let us plan for the horizontal data partitioning:
1. Split the table into multiple tables (partitions) with the same table structure storing
different data sets.
2. All the constraints and triggers to manage the business rules must be maintained
across the partitions.
3. To split the table horizontally, the analysis on data is essential which is an optimal
way to split the table. For instance, the sales history data can be partitioned based on
the sale date, customer type, or product attribute.
4. The syntax for designating a table's filegroup is as follows:
CREATE TABLE ...[ ON {filegroup | "default" }]
[ { TEXTIMAGE_ON { filegroup | "default" } ]
5. Let us demonstrate the table on a non-default newly created filegroup:
ALTER DATABASE AdventureWorks2008R2
ADD FILEGROUP ADW_FG2
GO
ALTER DATABASE AdventureWorks2008R2
ADD FILE ( NAME = ADW_F2,
FILENAME = 'F:\Data\ADW_F2.ndf',
SIZE = 1024MB
) TO FILEGROUP ADW_FG2
GO
ALTER DATABASE AdventureWorks2008R2
ADD FILE ( NAME = ADW_IDX,
FILENAME = 'F:\Data\ADW_IDX.ndf',
SIZE = 1024MB
) TO FILEGROUP ADW_INDX
GO
6. Let us create a new table on this new filegroup as follows:
CREATE TABLE Sales.ADWCompany(
ADWCompanyID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
ParentCompanyID int NULL,
ParentCompanyName varchar(25) NOT NULL,
CreateDate datetime NOT NULL DEFAULT (getdate())
) ON ADW_FG2
Search WWH ::
Custom Search