SQL Server database partition

The main purpose of using partition tables is to improve the scalability and availability of large tables and tables with various access modes.Management. Large tables are tables with a large amount of data. The access mode is a different data row set that needs to be accessed for different purposes. Access for each purpose can be called an access mode. Partitioning can divide data into smaller and easier-to-manage parts to improve performance. On the other hand, for systems with multiple CPUs, partitions can be performed in parallel for table operations, which is very helpful for improving performance.

 

Let's start from the next step to explain the SQL database partitioning steps:

1. Query

-- Finally, we can query whether the inserted data is actually partitioned into four table partitions in different file groups.

SELECT *, $PARTITION.[Data Partition Range](ID) FROM MyTable

Mytable indicates a partition table I created. $ partition. [Data Partition range] (ID) is the partition

 

2. Add data

USE [Data Partition DB2]godeclare @count intset @count =-25while @count <=100begininsert into MyTable select @count,getdate(),100.00set @count=@count+1endset @count =101while @count <=200begininsert into MyTable select @count,getdate(),200.00set @count=@count+1endset @count =201while @count <=300begininsert into MyTable select @count,getdate(),300.00set @count=@count+1endset @count =301while @count <=400begininsert into MyTable select @count,getdate(),400.00set @count=@count+1endset @count =401while @count <=800begininsert into MyTable select @count,getdate(),500.00set @count=@count+1end 

This step is no different from the common one, that is, you want to add data to the table.

 

 

3. Create a table Field and its growth-dependent partition Architecture

-- Create a table structure

USE [Data Partition DB2]goCREATE TABLE MyTable(ID INT NOTNULL, Date DATETIME, Cost money)      ON [Data Partition Scheme] (ID);

 

-- Note: On [Data Partition Scheme] (ID); indicates that the partition is based on the size of the ID, and the name of the Shard to be purchased after on

-- Finally, we can fill in the data.

 

 

4. Create a database partition Architecture

Next, we will create a partition architecture to apply the partition function to the four file groups we have divided.

USE [Data Partition DB2]goCREATE PARTITION SCHEME [Data Partition Scheme]AS PARTITION [Data Partition Range]TO ([Data Partition DB FG1], [Data Partition DB FG2],[Data Partition DB FG3],[Data Partition DB FG4]); 

 

5. Create a partition table function. This can be understood as a rule. The following type (INT) indicates that the type of the field to be partitioned is int.

use [Data Partition DB2]GOCREATE PARTITION FUNCTION [Data PartitionRange](int)AS RANGE LEFT FOR VALUES (100,200,300) 

 

6. Create master database file primary and 4 separate file group FG1-FG4

USE [master]GO/******Object:  Database [Data PartitionDB]    Script Date: 10/08/2006 23:09:53******/IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Data Partition DB2')DROP DATABASE [Data Partition DB2]GOCREATE DATABASE [Data Partition DB2]ON PRIMARY(NAME='DataPartition DB Primary FG',FILENAME='F:\Data2\Primary\DataPartition DB Primary FG.mdf',SIZE=5,MAXSIZE=500,FILEGROWTH=1 ),FILEGROUP [Data Partition DB FG1](NAME = 'DataPartition DB FG1',FILENAME ='F:\Data2\FG1\DataPartition DB FG1.ndf',SIZE = 5MB,MAXSIZE=500,FILEGROWTH=1 ),FILEGROUP [Data Partition DB FG2](NAME = 'DataPartition DB FG2',FILENAME ='F:\Data2\FG2\DataPartition DB FG2.ndf',SIZE = 5MB,MAXSIZE=500,FILEGROWTH=1 ),FILEGROUP [Data Partition DB FG3](NAME = 'DataPartition DB FG3',FILENAME ='F:\Data2\FG3\DataPartition DB FG3.ndf',SIZE = 5MB,MAXSIZE=500,FILEGROWTH=1 ),FILEGROUP [Data Partition DB FG4](NAME = 'DataPartition DB FG4',FILENAME ='F:\Data2\FG4\DataPartition DB FG4.ndf',SIZE = 5MB,MAXSIZE=500,FILEGROWTH=1 )