今天簡單試了一下表分區,大概過程如下
1.為分區創建存儲文件
ALTER DATABASE nutrition ADD FILEGROUP bmi0
ALTER DATABASE nutrition ADD FILEGROUP bmi1
ALTER DATABASE nutrition ADD FILEGROUP bmi2
ALTER DATABASE nutrition ADD FILEGROUP bmi3
2. 為文件組設置存儲文件
ALTER DATABASE nutrition ADD FILE (NAME = ‘bmi0′, FILENAME = ‘D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\nutrition_bmi0.NDF’) TO FILEGROUP bmi0;
ALTER DATABASE nutrition ADD FILE (NAME = ‘bmi1′, FILENAME = ‘D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\nutrition_bmi1.NDF’) TO FILEGROUP bmi1;
ALTER DATABASE nutrition ADD FILE (NAME = ‘bmi2′, FILENAME = ‘D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\nutrition_bmi2.NDF’) TO FILEGROUP bmi2;
ALTER DATABASE nutrition ADD FILE (NAME = ‘bmi3′, FILENAME = ‘D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\nutrition_bmi3.NDF’) TO FILEGROUP bmi3;
3. 創建分區函數
CREATE PARTITION FUNCTION [pf_bmi](int)
AS
RANGE Right FOR VALUES (’1′,’2′,’3′)
4. 創建分區方案
CREATE PARTITION SCHEME ps_bmi
AS
PARTITION pf_bmi TO (bmi0,bmi1,bmi2,bmi3)
5. 創建分區表
CREATE TABLE [dbo].[kk_bmi](
[bmi_id] [int] IDENTITY(1,1) NOT NULL,
[age] [int] NOT NULL,
[sex] [bit] NOT NULL,
[hxhistory] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[season] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[bmi_index] [float] NOT NULL,
[evaluate] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[suggestion] [nvarchar](500) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON ps_bmi(bmi_id)