SQL SERVER Performance Tuning four: Creating a partitioned Table

There should be different filegroups in the database before the partition table is created;

Assuming the database name is Df17datapro, create the user table UserID, put the first 500,000 data in the primary filegroup, place the 50~100 in the FG1 filegroup, and put more than 100 in the FG2 filegroup

1. Add File group Fg1,fg2

ALTER  DATABASE Df17datapro ADD FILEGROUP FG1

ALTER  DATABASE df17dataproadd FILEGROUP fg2

2. Adding files to a filegroup

ALTER TABLEDf17dataproADD FILE(NAME=Df17datapro1,filename='D:\DF17DATAPRO.NDF') toFILEGROUP fg1ALTER TABLEDf17dataproADD FILE(NAME=Df17datapro2,filename='E:\DF17DATAPRO.NDF') toFILEGROUP FG2

3. Create a partition function

CREATE Partitionfunction myrangefunction (int)
As RANGE right for VALUES (500000,1000000)
GO

4. Create partition scheme based on partition function

CREATE PARTITION SCHEME Myrangeschema  as PARTITION  Myrangefunctionto (PRIMARY, FG1,FG2)

5. Specify ID column to create user table as partition scheme

CREATE TABLE USER  int notNULL   varchar (null  on Myrangeschema (UserID)

SQL SERVER Performance Tuning four: Creating a partitioned Table