Table partitioning should be one of the best ways to handle large amounts of data. Nominally a table, but stored in separate physical files, is not much different from a small table.
However, care must be taken with the primary key of a partitioned table.
The best column to partition a table against is the primary key. For example, in a user table, the UserId is an automatically growing identity column, the primary key, and is used as the basis for partitioning, one partition per 500,000. Here, the primary key is perfectly combined with the partition based column to protect hair and scalp. But sometimes the primary key is not necessarily the partitioning column. For example, the user Settings table. There is a one-to-many relationship between the user table and the user Settings table. One user has many Settings, that is, each user has several Settings records. In this case, the UserId should be the partition reference column, but obviously cannot be the primary key because it is not unique.
Typically, the primary key defaults to clustered indexes. The clustered index is part of the data, meaning that the clustered index and table data must be the same file group. How can the table be partitioned if the primary key is not partitioned by column and the index is aggregated? I tried to partition a large table with no primary key, and then created the primary key into a separate file group, only to find that the table data was also moved to that separate file group.
My feeling is that in this case, the primary key should not be a clustered index, but should be a non-clustered index.
Do not base indexes on this partition except for reference columns (called alignment), specify separate file groups for all indexes, or even their own partitions. Otherwise, the system will sneak you a listing.
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — (the following is copied from the SQL SERVER 2008 online help) Partitioning a clustered index When partitioning a clustered index, the clustered key must contain the partitioning reference column. When partitioning a non-unique clustered index, if the partitioning basis column is not explicitly specified in the clustered key, by default SQL Server adds the partitioning basis column to the list of clustered index keys. If the clustered index is unique, you must explicitly specify that the clustered index key contains the partitioning reference column.
Partitioning a non-clustered index When partitioning a unique non-clustered index, the index key must contain the partitioning reference column. When a non-unique non-clustered index is partitioned, by default SQL Server adds partitions as non-key (inclusive) columns of the index by column to ensure that the index is aligned with the base table. SQL Server will not add a partitioning basis column to an index if it already exists.
—————————————————————————————
CREATE TABLE [dbo].[User_News_Log]([LogId] [int] IDENTITY(1,1) NOT NULL, [WebUserId] [int] NOT NULL, [Code] [varchar](20) NOT NULL, [Content] [varchar](700) NOT NULL, [CreateDate] [smalldatetime] NOT NULL, — CONSTRAINT [PK_user_news] PRIMARY KEY NonCLUSTERED ([LogId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [UserNewsIndex]) — Partition table ON UserNewsSCM(CreateDate) — page-level compression WITH (DATA_COMPRESSION = PAGE ON PARTITIONS (1 TO 5) ); GO
One way to convert an existing unpartitioned table into a partitioned table is to create a clustered index on the partition. When it’s done, you’ll be delighted to find that the table has been partitioned.
Attached: an example of existing large table partitioning, compression, and primary key repartitioning
Use [Compare] go — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — – data ——————————————————————-
ALTER DATABASE [Compare] ADD FILEGROUP [Compare0] GO ALTER DATABASE [Compare] ADD FILEGROUP [Compare1] GO ALTER ALTER DATABASE [Compare] ADD FILE (NAME = N’Compare0′,FILENAME = N’C:/Compare/Compare0.ndf’,SIZE = 3MB,FILEGROWTH = 1MB) TO FILEGROUP [Compare0] GO ALTER DATABASE [Compare] ADD FILE (NAME = N’Compare1′,FILENAME = N’C:/Compare/Compare1.ndf’,SIZE = 3MB,FILEGROWTH = 1MB) TO FILEGROUP [Compare1] GO ALTER DATABASE [Compare] ADD FILE (NAME = N’Compare2′,FILENAME = N’C:/Compare/Compare2.ndf’,SIZE = 3MB,FILEGROWTH = 1MB) TO FILEGROUP [Compare2] GO
CREATE PARTITION FUNCTION ComparePFN(INT) AS RANGE LEFT FOR VALUES (50000100000); GO
CREATE PARTITION SCHEME CompareSCM AS PARTITION ComparePFN TO ([Compare0],[Compare1],[Compare2]); GO
Build clustered index to partition scheme CREATE CLUSTERED INDEX [cix_cp_wk] ON [dbO].[CatchMirror] ([WebSiteKeyWordId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON CompareSCM(WebSiteKeyWordId) GO — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — row compression —————————————————————– ALTER TABLE [CatchMirror] REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = ROW ON PARTITIONS(1 TO 3) ); GO — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — partitioning index (primary key) ——————————————————————- ALTER DATABASE [Compare] ADD FILEGROUP [CompareIndex0] GO ALTER DATABASE [Compare] ADD FILEGROUP [CompareIndex1] GO ALTER DATABASE [Compare] ADD FILEGROUP [CompareIndex2] GO ALTER DATABASE [Compare] ADD FILE (NAME = N’CompareIndex0′,FILENAME = N’C:/Compare/CompareIndex0.ndf’,SIZE = 3MB,FILEGROWTH = 1MB) TO FILEGROUP [CompareIndex0] GO ALTER DATABASE [Compare] ADD FILE (NAME = N’CompareIndex1′,FILENAME = N’C:/Compare/CompareIndex1.ndf’,SIZE = 3MB,FILEGROWTH = 1MB) TO FILEGROUP [CompareIndex1] GO ALTER DATABASE [Compare] ADD FILE (NAME = N’CompareIndex2′,FILENAME = N’C:/Compare/CompareIndex2.ndf’,SIZE = 3MB,FILEGROWTH = 1MB) TO FILEGROUP [CompareIndex2] GO CREATE PARTITION FUNCTION CompareIndexPFN(INT) AS RANGE LEFT FOR VALUES (200000000,400000000); GO CREATE PARTITION SCHEME CompareIndexSCM AS PARTITION CompareIndexPFN TO ( [CompareIndex0] ,[CompareIndex1] ,[CompareIndex2] ); GO
— Primary key, non-clustered, partition, ALTER TABLE [dbO].[CatchMirror] ADD CONSTRAINT [PK_CatchMirror] PRIMARY KEY NONCLUSTERED ([CatchMirrorId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,DATA_COMPRESSION = ROW ) ON CompareIndexSCM(CatchMirrorId) GO
Postscript:
The performance improvement of application partitioning is significant. The number of server disk queues exceeds 30 before partition and compression. After partitioning and compression, the hard disk queue is less than 10, a full three-fold improvement.
\
A sideband: If the table has already applied page-level compression, clustered indexes should not apply row compression, otherwise the entire table will become row compression again.
2, split partition:
Split partitions. Unless there are redundant file groups in the partition scheme, add file groups first, then modify the partition scheme, and then modify the partition function. * * * *
ALTER PARTITION SCHEME UserSCM NEXT USED [user5];
ALTER PARTITION FUNCTION UserPFN() SPLIT RANGE(‘5’)
3, partition according to the column character type
CREATE PARTITION FUNCTION UserPFN(VARCHAR(50)) AS RANGE LEFT FOR VALUES ( ‘1’,’4′,’7′,’a’,’c’,’e’,’h’,’k’,’o’,’r’,’u’,’x’,’z’); GO
4, partition according to the date type
CREATE PARTITION FUNCTION UserPFN(SMALLDATETIME) AS RANGE LEFT FOR VALUES (‘ 2008-01-01 ‘, ‘2009-01-01’, ‘2010-01-01’, ‘2011-01-01’, ‘2012-01-01’); GO\