SQL Server partition Drill

Source: Internet
Author: User
Tags filegroup

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.