CodeAdd comments to help beginners.
Use [ Master ]
Go
If Exists ( Select * From SYS. Databases Where Name = ' Test_1 ' )
Drop Database Test_1
Go
-- Create a new database and drill down the partitions. Therefore, we will create two more file groups test_a and test_ B for use in the later partitioning scheme.
Create Database [ Test_1 ] On Primary
(Name = N ' Test_1 ' , Filename = N ' D: \ sqldata \ test_1.mdf ' , Size = 10240kb, maxsize = Unlimited, filegrowth = 1024kb ),
Filegroup [ Test_a ]
(Name = N ' Test_a ' , Filename = N ' D: \ sqldata \ test_a.ndf ' , Size = 1024kb, maxsize = Unlimited, filegrowth = 1024kb ),
Filegroup [ Test_ B ]
(Name = N ' Test_ B ' , Filename = N ' D: \ sqldata \ test_ B .ndf ' , Size = 1024kb, maxsize = Unlimited, filegrowth = 1024kb)
Log On
(Name = N ' Test_log ' , Filename = N ' D: \ sqldata \ test_log.ldf ' , Size = 7616kb, maxsize = 2048 GB, filegrowth = 10 % )
Collate chinese_prc_ci_as
Go
Use [ Test_1 ]
Go
-- If the partition function exists, drop it first.
If Exists ( Select * From SYS. partition_functions Where Name = N ' Test_partition ' )
Drop Partition Function [ Test_partition ]
Go
/**/ /*The partition function is used to create a partition function. The partition function is simple to specify a range to determine why a value is stored in that partition.*/
-- Create a simple partition function. The function is divided into two zones by 1000.
Create Partition Function Test_partition ( Int )
As
Range Left For Values ( 1000 )
Go
/**/ /*Check whether the partition scheme exists. If yes, drop it first.*/
If Exists ( Select * From SYS. partition_schemes Where Name = N ' Test_scheme ' )
Drop Partition Scheme test_scheme
Go
-- Create a partition scheme. Specify a partition function and the file group in which the partition function is located.
Create Partition Scheme test_scheme
As
Partition [ Test_partition ] To (Test_a, test_ B)
Go
-- Create a partition table
If Object_id ( ' Student ' , ' U ' ) Is Not Null
Drop Table Student;
Go
Create Table Student
(
ID Int Identity ( 1 , 1 ) Not Null ,
Name Varchar ( 10 ) Not Null ,
Class Int Not Null ,
Grade Int
) On Test_scheme (class) -- Specify the partition scheme to be used in this table, and specify the partition based on the column
Go
-- Insert several data entries at will
Insert Into Student Values ( ' Aqu ' , 10 , 100 ); -- This data is stored in partition.
Insert Into Student Values ( ' Aqu _ Boundary ' , 1000 , 89 ); -- The data here is also a boundary in partition A, because we specified range left in the function above, so 1000 is on partition.
Insert Into Student Values ( ' Bqu ' , 1001 , 90 ); -- This one must be on partition B.
Go
-- Finally, let's look at the results. $ Partition. The Partition Function (partition column) can return the partition sequence number of a row.
Select * , Partition Number = $ Partition. test_partition (class) From Student
Go
