SQL Server automated partitioning Solution

This article is my thoughts on the database partition solution, and may have some problems. For discussion only. SQL Server (SqlServer2005SqlServer2008) implements partitions under the Enterprise Edition. SQL Server partitions are divided into the following processes: 1. Create a file group to store data files. 2. Create a file group user data file. 3. Create a file group.

This article is my thoughts on the database partition solution, and may have some problems. For discussion only. (SqlServer 2005 \ SqlServer 2008) Partitions must be performed under the Enterprise Edition. SQL Server partitions are divided into the following processes: 1. Create a file group to store data files. 2. Create a file group user data file. 3. Create a file group.

This article is my thoughts on the database partition solution, and may have some problems. For discussion only. (SqlServer 2005 \ SqlServer 2008) Partitions must be performed under the Enterprise Edition.

SQL Server partitions are divided into the following processes: 1. Create a file group to store data files 2. Create a file group user data file 3. Create a partition function 4. Create a partition scheme 5. Create a table under the partitioning scheme

This article is completed in SqlServer2012.

Process:

1. Create a database and create files and file groups in the attributes. For example:

　　

You can select a file group or create a file group to store new files:

　　

2. Create a partition function

CREATE PARTITION FUNCTION [partitionById](int) AS RANGE LEFT FOR VALUES (100, 200, 300)

3. Create a partition scheme

Create partition scheme [partitionSchemeById] as partition [partitionById] -- PARTITION Function TO ([FileGroup1], [FileGroup2], [FileGroup3], [FileGroup4])

Note that the preceding partition function uses LEFT, which specifies how to store data in the database based on the following values. The above storage methods are:-∞, 100], (100,200], (200,300], (300, + ∞). This partition scheme is based on the Partition Function

PartitionById. That is to say, the storage interval of the above IDS is put in the files of [FileGroup1], [FileGroup2], [FileGroup3], and [FileGroup4] File groups respectively.

4. Create a table based on the partition scheme

CREATE TABLE [dbo].[Account](    [Id] [int] NULL,    [Name] [varchar](20) NULL,    [Password] [varchar](20) NULL,    [CreateTime] [datetime] NULL) ON partitionSchemeById(Id)

Note: In the table creation script, you must specify the partition scheme and partition basis columns.

View the data of a partition:

SELECT * FROM [dbo].[Account]WHERE $PARTITION.[partitionById](Id)=1

The query result is as follows:

At this point, the partition seems to have ended. But let's look at the data in the next partition: Id> = 400 is all stored in a data file. This may cause bottlenecks in this partition.

If data continues to grow, we hope that the number of partitions increases automatically. For example, a new partition is generated every day to store new data in the partition. For example, by the second day, a new partition is generated to store (400,500] of data.

Here I use SQL Job to automatically generate partitions:

DECLARE @ maxValue INT, @ secondMaxValue INT, @ differ INT, @ fileGroupName VARCHAR (200), @ fileNamePath VARCHAR (200), @ fileName VARCHAR (200), @ SQL NVARCHAR (1000) SET @ fileGroupName = 'filegroup' + REPLACE (CONVERT (varchar, GETDATE (), 120 ),'-',''),'',''), ':', '') PRINT @ fileGroupNameSET @ SQL = 'alter DATABASE [Test] ADD filegroup' + @ fileGroupNamePRINT @ sqlEXEC (@ SQL) SET @ fileNamePath = 'C: \ Program Files \ Microsoft SQL Server \ MSSQL11.MSSQLINSTANCE \ MSSQL \ DATA \ '+ REPLACE (CONVERT (varchar, GETDATE (), 120 ),'-', ''),'', ''), ':','') + '. NDF 'set @ fileName = n' file' + REPLACE (CONVERT (varchar, GETDATE (), 120 ),'-',''),'', ''), ':','') SET @ SQL = 'alter DATABASE [Test] ADD FILE (NAME = ''' + @ fileName + ''', FILENAME = n''' + @ fileNamePath + ''') to filegroup '+ ''+ @ fileGroupNamePRINT @ sqlPRINT 1 EXEC (@ SQL) PRINT 2 -- modify the partition scheme, use a new file group to store the NEXT newly added data SET @ SQL = 'alter partition scheme [partitionSchemeById] next used '+ ''+ @ fileGroupNameEXEC (@ SQL) -- partition architecture PRINT 3 SELECT @ maxValue = CONVERT (INT, MAX (value) from sys. PARTITION_RANGE_VALUES PRVSELECT @ secondMaxValue = CONVERT (INT, MIN (value) FROM (select top 2 * from sys. PARTITION_RANGE_VALUES order by value desc) prv set @ differ = @ maxValue-@ secondMaxValue alter partition function partitionById () -- partition function split range (@ maxValue + @ differ)

In this way, you can specify when to run each day in the plan ,:

Reference: http://www.cnblogs.com/lyhabc/articles/2623685.html