Create a partition table

-- Create a partition function
Create Partition Function pf_orders_orderdaterange (datetime) as range right for values ('2017-01-01 ', '2017-01-01', '2017-01-01 ')
Go
-- Create a partition scheme
Create Partition Scheme ps_orders as partition pf_orders_orderdaterange to ([primary], [primary], [primary], [primary]) Go
-- Create a partition table
Create Table DBO. Orders (orderid int not null, customerid varchar (10) Not null, employeeid int not null, orderdate datetime not null) on ps_orders (orderdate) Go
-- Create a clustered partition Index
Create clustered index ixc_orders_orderdate on DBO. Orders (orderdate) Go
-- Set the primary key for the partition table
Alter table dbo. Orders add constraint pk_orders primary key (orderid, customerid, orderdate) Go
-- Import data to a partition table
Insert into DBO. Orders select orderid, customerid, employeeid, orderdate from DBO. orders_from_sql2000_northwind
-- (Note: the data comes from the SQL Server 2000 sample database)
Go
-- View the data distribution of each partition in a partition table
Select partition = $ partition. pf_orders_orderdaterange (orderdate), rows = count (*), minval = min (orderdate), maxval = max (orderdate) from DBO. orders group by $ partition. pf_orders_orderdaterange (orderdate) order by partition go

-- Method 2 ============================================ ==========================================

Create Database DB
On (name = dB, filename = 'I: \ dB. MDF '),
Filegroup FG1
(Name = FG1, filename = 'I: \ data1.ndf '),
Filegroup FG2
(Name = FG2, filename = 'I: \ data2.ndf '),
Filegroup FG3
(Name = FG3, filename = 'I: \ data3.ndf '),
Filegroup fg4
(Name = fg4, filename = 'I: \ data4.ndf ')
Log On (name = db_log, filename = 'I: \ db_log.ldf ');

 

Go

Use dB;
Go

Create Partition Function pf_quarter (INT)
As range right
For values (4,7, 10 );

Create Partition Scheme ps_quarter
As partition pf_quarter
To (FG1, FG2, FG3, fg4 );
Go

Create Table DBO. Tb (ID int not null, date datetime not null, [month] int not null)
On ps_quarter ([month]);

Create unique clustered index uiq_month_tb on TB (ID, [month]);

-- Insert Test
Insert into DBO. Tb (ID, date, [month])
Select number, dateadd (day, number, '2017-01-01 '), month (dateadd (day, number, '2017-01-01 '))
From master. DBO. spt_values where type = 'P ';

-- Method 3 ============================================ ==========================================
-- Add a file group
Alter database T1 add filegroup [FG1]
Alter database T1 add filegroup [FG2]
Alter database T1 add filegroup [FG3]
Alter database T1 add filegroup [fg4]
-- Add different file pairs to the file group
Alter database T1 add file (name = n'fg1 ', filename = n'c: \ fg1.ndf', size = 3072kb, filegrowth = 1024kb) to filegroup [FG1]
Alter database T1 add file (name = n'fg2 ', filename = n'c: \ fg2.ndf', size = 3072kb, filegrowth = 1024kb) to filegroup [FG2]
Alter database T1 add file (name = n 'fg3', filename = n 'C: \ fg3.ndf', size = 3072kb, filegrowth = 1024kb) to filegroup [FG3]
Alter database T1 add file (name = n 'fg4', filename = n 'C: \ fg4.ndf', size = 3072kb, filegrowth = 1024kb) to filegroup [fg4]
-- For Io balancing, the files can be distributed in different disks,
-- Alter database T1 add file (name = n'fg2 ', filename = n'h: \ fg2.ndf', size = 3072kb, filegrowth = 1024kb) to filegroup [FG2]
-- H can be a disk on the server or an external disk.

 

-- Create a partition function
Create Partition Function numpartitionfun (INT) as range left for values (100,500,800 );

-- Specify the Partition Scheme
Create Partition Scheme numpartitionscheme as partition numpartitionfun to ([FG1], [FG2], [FG3], [fg4]);

-- When the event is finished, it will only fail

Create Table Nums (n int) on numpartitionscheme (N)

-- Fill table
Declare @ Max as int, @ RC as int;
Set @ max = 1000
Set @ rc = 1;
Insert into Nums values (1)
While @ rc * 2 <= @ Max
Begin
Insert into Nums select N + @ RC from Nums
Set @ rc = @ rc * 2
End
Insert into Nums
Select N + @ RC from Nums where N + @ RC <= @ Max

-- Query partition information
Select $ partition. numpartitionfun (n) as Pn, min (n) as start, max (n) [end] From Nums group by $ partition. numpartitionfun (n) order by $ partition. numpartitionfun (N)